重建公司kardex

Question

我正试图重建我的公司kardex。我需要使用“平均成本方法”，该平均成本方法应该考虑每个交易在其特定日期时间的平均成本。

我有一个简单的表格，包含所有传入/传出库存事务（所有动作）。

每笔进货交易（采购）的价格都正确，因为我从相应的发票中获取单位成本。

问题： 所有销售交易（即期库存）都没有单位成本（因为我需要重建这些成本）。 我知道我可以使用游标，但我们都知道那里的性能问题。

我准备了一个简单的数据来更好地解释我自己。 请注意，所有销售（即期交易）都没有成本，我认为使用简单的窗口函数不会有帮助，因为每一行都取决于以前的计算。所以，我需要根据以前的计算计算..？

DECLARE @Resultado TABLE (Row_ID BIGINT IDENTITY NOT NULL PRIMARY KEY, Inventory_Name VARCHAR(100), Transaction_Date DATETIME, DIRECTION BIT, Transaction_Name VARCHAR(100),Transaction_Quantity INT, Transaction_UnitCost NUMERIC(18,2), Transaction_Amount NUMERIC(18,2), AVERAGE_UNITCOST_AT_TRANSACTION_DATE NUMERIC(18,2)) 

INSERT INTO @Resultado (Inventory_Name , Transaction_Date , DIRECTION , Transaction_Name , Transaction_Quantity , Transaction_UnitCost, Transaction_Amount, AVERAGE_UNITCOST_AT_TRANSACTION_DATE) 
SELECT 'COMPUTER', '2017-01-01', 1, 'INCOMING PRODUCT; PRUCHASE', 100, 10, 100 * 10, 10 
UNION ALL SELECT 'COMPUTER', '2017-01-02', 1, 'INCOMING PRODUCT; PURCHASE', 105, 11, 105 * 11, 10.51 
UNION ALL SELECT 'COMPUTER', '2017-01-03', 1, 'INCOMING PRODUCT; PURCHASE', 110, 12, 110 * 12, 11.03 
UNION ALL SELECT 'COMPUTER', '2017-01-04', 0, 'OUTGOING PRODUCT; SALES', -200, NULL, NULL, NULL 
UNION ALL SELECT 'COMPUTER', '2017-01-05', 0, 'OUTGOING PRODUCT; SALES', -50, NULL, NULL, NULL 
UNION ALL SELECT 'COMPUTER', '2017-01-06', 1, 'INCOMING PRODUCT; PURCHASE', 110, 10, 110 * 10, NULL 
UNION ALL SELECT 'COMPUTER', '2017-01-07', 0, 'OUTGOING PRODUCT; SALES', -20, NULL, NULL, NULL 
UNION ALL SELECT 'COMPUTER', '2017-01-08', 0, 'OUTGOING PRODUCT; SALES', -20, NULL, NULL, NULL 
UNION ALL SELECT 'COMPUTER', '2017-01-09', 0, 'OUTGOING PRODUCT; SALES', -20, NULL, NULL, NULL 

SELECT * FROM @Resultado 

Answer 1

提示：如果您在问题中包含了预期结果，这将会很有帮助。

此公用表表达式（CTE）应该让你开始：

with CTE as (
    select Row_Id, Inventory_Name, Transaction_Date, Direction, Transaction_Name, 
    Transaction_Quantity, Transaction_UnitCost, 
    Transaction_Quantity as QuantityOnHand, 
    Transaction_UnitCost as AverageUnitCost 
    from @Resultado 
    where Direction = 1 and Row_Id = 1 -- Starting condition for your single product sample data. 
    union all 
    select R.Row_ID, R.Inventory_Name, R.Transaction_Date, R.Direction, R.Transaction_Name, 
    R.Transaction_Quantity, R.Transaction_UnitCost, 
    -- The TransactionQuantity is already signed, so there is no need to multiple it by (Direction * 2 - 1) . 
    CTE.QuantityOnHand + R.Transaction_Quantity, 
    -- My accounting is pretty rusty, but this should do some sort of useful averaging. 
    Cast(case 
     when R.Direction = 0 then CTE.AverageUnitCost -- Sales don't affect the average unit cost. 
     else (CTE.AverageUnitCost * CTE.QuantityOnHand + R.Transaction_UnitCost * R.Transaction_Quantity)/
     (CTE.QuantityOnHand + R.Transaction_Quantity) end 
     as Numeric(18,2)) 
    from CTE inner join 
     @Resultado as R on R.Row_Id = CTE.Row_Id + 1 -- Row by row. 
    ) 
    select Row_Id, Inventory_Name, Transaction_Date, Direction, Transaction_Name, Transaction_Quantity, Transaction_UnitCost, 
    QuantityOnHand, AverageUnitCost 
    from CTE; 

您可以添加一个分号到您select发言结束后，对你的样本数据运行它。

由于您需要从第一行开始计算，因此性能可能很差。添加列以维护QuantityOnHand和AveragreUnitCost将允许您在发生新事务时更新数据，例如，在触发器中。 （请注意，更新和删除操作可能需要从一开始就重新计算。）

更现代的解决方案将使用Lead/Lag，但我碰巧回到了SQL Server 2008 R2。