永久加权平均成本计算SQL Server 2008 [英] Perpetual Weighted Average cost Calculation SQL Server 2008

问题描述

我想计算库存的永久加权平均成本。我试图了解以下链接
的解决方案 SQL中的库存平均成本计算
但无法获取，这对我来说很复杂。

I want to calculate Perpetual Weighted Average cost for inventory. I tried to understand a solution on following link Inventory Average Cost Calculation in SQL but cannot get it, it is complex for me.

这是我的数据库详细信息。

here is my database details.

Purch_ID  Item_ID   QTY    Unit_Price   

 01         1       10       10               
 02         2       10       20               
 03         3       20       15              
 04         2       10       20               
 05         1       10       18              
 06         2       25       17      

我要使用以下公式计算每次购买后的加权平均费用

I want to calculate the Weighted Average cost after each Purchase using following formula

((old_stock x Old unit price)+(New_Purchase_qty x New unit price))/(old stock qty + new purchase qty)

有什么建议吗？

推荐答案

如果我正确理解，则需要累计平均价格。

If I understand correctly, you want the cumulative average price.

此方法使用子查询来计算累计总数量和累计支付总额。该比率为平均成本：

This approach uses subqueries to calculate the cumulative total quantity and the cumulative total paid. The ratio is the avg cost:

select t.*, cumepaid / cumeqty as avg_cost
from (select t.*,
             (select SUM(qty) from t t2 where t2.item_id = t.item_id and t2.purch_id <= t.purch_id
             ) as cumeqty,
             (select SUM(qty*unit_price) from t t2 where t2.item_id = t.item_id and t2.purch_id <= t.purch_id
             ) as cumepaid
      from t
     ) t

在SQL Server 2012中，您可以通过直接计算累积总和来做到这一点（应该更有效）。您也可以通过 cross apply 来做到这一点，但我更喜欢标准SQL。

In SQL Server 2012, you can do this by directly calculating cumulative sums (should be more efficient). You could also do this with cross apply, but I prefer standard SQL.

这篇关于永久加权平均成本计算SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！