sql - 计算 SQL Server 中的平均速率

Question

declare @table as table
(
    LedgerId int,
    Transaction_Type varchar(50),
    Inward_qty decimal (18,3),
    Inward_rate decimal (18,3)
)    

insert into @table 
values (1, 'Issue', 67.320, 473.66),
       (2, 'Receipt', 201.290, 657.90),
       (3, 'Receipt', 94.860, 473.66)

select * from @table

我想计算公式找到收据的平均费率，从剩余的问题中减去将乘以我们将拥有的值的费率，而不是总价值除以总收据数量，我们将得到一个内向费率我不知道如何做这个

公式

• 201.290 - 67.320 = 133.970（收据 - 发行）
• 133.970 * 657.900 = 88,138.863（剩余收据数量*费率）将获得价值
• 94.860 * 473.660 = 44,931.3876(receipt * rate) 将获得价值
• 88,138.863 + 44,931.3876 = 1,33,070.2506（总值）
• 133.970 + 94.860 = 228.83（总数量之和）
• 1,33,070.2506 / 228.83（总值之和）/（总量之和）= 581.5244967880086 因此581.524是平均费率

按上述计算输出布局

declare @table1 as table
(
LedgerId int,
Transaction_Type varchar(50),
Inward_qty decimal (18,3),
Inward_rate decimal (18,3)

)
 insert into @table1 values(1,'Issue',67.320,473.66),(2,'Receipt',133.970,657.90),(3,'Receipt',94.860,473.66)
 
 select * , inward_Value = Inward_qty * Inward_rate into #temp from @table1

 declare @Qty decimal (18,3)
 declare @value decimal (18,3)

 set @Qty= (select sum(Inward_qty) from #temp where Transaction_Type ='Receipt' )
 set @value =(select sum(inward_Value) from #temp where Transaction_Type ='Receipt' )

 declare @Rate decimal (18,3) = @Value/@Qty
 select @Rate


 drop table #temp

标签： sqlsql-server