Fifo汇总表

Question

​

​

我的要求是从生产日期批次中找出每个项目的库存余额。如果我们得到一个订单，就会按日期升序排列。例如，如果项目A有3个生产日期批次，每个批次有5个单位，当我得到12个单位的订单时，它应该按升序运行所有批次，并更新column.so中每个批次的余额。这里我得到了12个单位的订单，所以1和2个批次耗尽，余额2将从第3个批次中取出，余额3应该为该行更新。

Answer 1

在100k行的情况下，基于指针的解决方案应该会提供合理的性能，所以这里的解决方案会产生您所期望的输出。

如果这在您的实际数据中产生了所需的输出，请将此答案标记为已接受，我将看看是否可以找到更有效的基于集合的解决方案来添加到此答案中。

;with cteStock as (
    SELECT * FROM (VALUES ('A', '2019-10-17', 60, 60)
        , ('B', '2019-09-02', 210, 309), ('B', '2020-03-30', 120, 0)
        , ('C', '2019-03-25', 144, 102), ('C', '2020-03-25', 144, 0)
        , ('C', '2021-03-25', 144, 0), ('D', '2019-02-10', 72, 577)
        , ('D', '2019-02-28', 150, 0), ('D', '2019-03-29', 176, 0)
        , ('D', '2019-11-29', 80, 0), ('D', '2020-01-30', 80, 0)
        , ('D', '2020-04-06', 60, 0)
        ) as Stock (Item, MFGDate, StockQty, OrderQty)
)SELECT *, 0 as OrderProcessed, 0 as BalanceStock
INTO #Stock FROM cteStock
ORDER BY Item, MFGDate

DECLARE @Item nvarchar(50)
DECLARE @ItemLast nvarchar(50)
DECLARE @MfgDate DATE
DECLARE @StockQty INT
DECLARE @OrderQty INT
DECLARE @ResidualDemand INT
DECLARE @OrderProcessed INT
DECLARE @BalanceStock INT
DECLARE @CumeDemand INT
DECLARE curStock CURSOR FOR  SELECT * FROM #Stock ORDER BY Item, MFGDate

OPEN curStock
FETCH NEXT FROM curStock
INTO @Item, @MfgDate, @StockQty, @OrderQty, @OrderProcessed, @BalanceStock
SET @ItemLast = ''
WHILE @@FETCH_STATUS = 0  BEGIN  
    IF (@ItemLast != @Item) BEGIN --Start a new part
        SET @ItemLast = @Item
        SET @ResidualDemand = 0
    END
    SET @ResidualDemand = @ResidualDemand + @OrderQty
    IF (@ResidualDemand > @StockQty) BEGIN
        SET @OrderProcessed = @StockQty
        SET @ResidualDemand = @ResidualDemand - @StockQty
        SET @BalanceStock = 0
    END ELSE BEGIN
        SET @OrderProcessed = @ResidualDemand
        SET @ResidualDemand = 0
        SET @BalanceStock = @StockQty - @OrderProcessed
    END
    UPDATE #Stock SET BalanceStock = @BalanceStock, OrderProcessed = @OrderProcessed
    WHERE Item = @Item AND MfgDate = @MfgDate
    FETCH NEXT FROM curStock
    INTO @Item, @MfgDate, @StockQty, @OrderQty, @OrderProcessed, @BalanceStock
END
CLOSE curStock;  
DEALLOCATE curStock;  

SELECT * FROM #Stock

DROP TABLE #Stock

我使用了一个临时表，因为我不知道您是否有权更改模式，但是为OrderProcessed和BalanceInStock添加列将允许就地计算，这样会更快。您甚至可以将其放在触发器中以使其自动化。

顺便说一句，我不知道你的情况是否允许，但是如果你重构你的解决方案来存储QtyProduced而不是QtyInStock，这将极大地提高像这样的查询的清晰度和效率。您可以为Produced和Ordered定义运行总数，一个简单的差异就会给出Backorder和StockOnHand。此外，您可以查看特定季度或一年的生产情况，找到较长的积压时间，等等。您甚至可以使这些计算的持久化列易于在其他地方使用。

您当前的配置很难解决，因为StockOnHand不是基本输入，它是一种系统状态，通过一个具有不连续性的函数与历史记录纠缠在一起-每个StockOnHand依赖于所有先前的生产，所有先前的订单，并且可能有它不显示的隐藏的缺货(没有缺货的产品和有100个缺货的产品的StockOnHand都为零)。