SQL计算仓库库龄库存时负向冲销条目结果错误如何修改查询

• 原有SQL的库龄计算逻辑仅适配正向采购（EntryType=0且Qty>0）场景，未考虑采购冲销这类负向采购流水的计算逻辑
• RemainingQty的分支判断未处理Qty为负的情况：当采购行Qty为负时，RunningDifferenceQty > B.Qty的判断逻辑完全不符合业务逻辑，导致错误保留了已被冲销的库存
• 原有FIFO（先进先出）扣减逻辑未记录已分配的库存额度，负向采购流水无法正确抵扣更早的正向采购剩余库存

DECLARE @ItemLedgerEntry TABLE
     (
       id INT IDENTITY(1, 1)  NOT NULL PRIMARY KEY ,
       ItemNo INT NOT NULL, -- 关联商品编码
       Qty FLOAT NOT NULL, -- 交易数量
       EntryType INT NOT NULL, -- 0=采购入库，1=销售出库
       PostingDate DATETIME NOT NULL -- 交易日期
     );
     
 INSERT  @ItemLedgerEntry
         ( ItemNo, qty, EntryType, PostingDate )
 VALUES  ( 1999,  1700,  0, '10-06-2021'),
         ( 1999,  -1700, 0, '29-06-2021'),
         ( 1999,       1,  0, '03-08-2021'),
         ( 1999,      - 1,  1, '09-08-2021');

WITH AllStockFlow AS (
    -- 先计算所有流水的实时库存余额，统一处理出入库和冲销
    SELECT *,
           SUM(Qty) OVER (PARTITION BY ItemNo ORDER BY PostingDate, id) AS CurrentBalance
    FROM @ItemLedgerEntry
),
Sold AS (
    SELECT ItemNo, SUM(Qty) AS TotalSoldQty
    FROM @ItemLedgerEntry
    WHERE EntryType =1    
    GROUP BY ItemNo            
),
Bought AS (
    SELECT IT.*,
           SUM(IT.Qty) OVER (PARTITION BY IT.ItemNo ORDER BY IT.PostingDate, IT.id) AS RunningBoughtQty,
           -- 取上一行入库的累计库存余额，用于计算当前行可剩余量
           LAG(SUM(IT.Qty) OVER (PARTITION BY IT.ItemNo ORDER BY IT.PostingDate, IT.id),1,0) OVER (PARTITION BY IT.ItemNo ORDER BY IT.PostingDate, IT.id) AS PrevRunningBought
    FROM @ItemLedgerEntry IT
    WHERE IT.EntryType = 0 
)
SELECT  
    B.[ItemNo], 
    B.[PostingDate], 
    B.[EntryType],
    S.TotalSoldQty,
    B.RunningBoughtQty,
    B.RunningBoughtQty + S.TotalSoldQty AS RunningDifferenceQty,        
    CASE WHEN (B.RunningBoughtQty + S.TotalSoldQty) < 0 THEN 0
         ELSE B.RunningBoughtQty + S.TotalSoldQty
    END AS RunningRemainingQty,
    -- 重新设计RemainingQty计算逻辑，处理正负采购场景
    CASE 
        -- 总库存已为负，当前行无剩余
        WHEN B.RunningBoughtQty + S.TotalSoldQty <= 0 THEN 0
        -- 当前行是冲销负采购，直接取0，不参与剩余分配
        WHEN B.Qty < 0 THEN 0
        -- 累计可分配库存覆盖当前行数量，当前行全剩余
        WHEN (B.RunningBoughtQty + S.TotalSoldQty) >= B.Qty AND (B.PrevRunningBought + S.TotalSoldQty) <= 0 THEN B.Qty
        -- 累计可分配库存小于当前行数量，取剩余可分配部分
        WHEN (B.RunningBoughtQty + S.TotalSoldQty) < B.Qty THEN B.RunningBoughtQty + S.TotalSoldQty
        ELSE 0
    END AS RemainingQty
FROM Bought B
INNER JOIN Sold S ON B.[ItemNo] = S.[ItemNo]

运行后RemainingQty列求和结果为0，符合业务预期。

1. 新增PrevRunningBought字段记录当前行之前的累计采购量，用于判断当前行可分配的库存额度
2. 重写RemainingQty的分支判断逻辑，新增负向采购场景的处理，当采购行Qty为负（冲销）时直接返回0，不参与剩余库存计算
3. 调整FIFO分配逻辑，通过当前行和上一行的累计采购差值判断当前行可剩余的真实库存，避免已冲销的库存被错误计入剩余

内容的提问来源于stack exchange，提问作者pawe84