基于行级结余结转更新SQL表的Available_SOH列

原始数据表格 #

需求说明 #

按Source、Loc、Bin、Item分组，每组内按DocNum升序排序：

• 取组内最早DocNum行的Available_SOH作为初始库存
• 每行结余 = 当前行Available_SOH - 当前行Required_SOH
• 将结余作为下一行的Available_SOH，依次滚动计算后续行的值

以分组(A, ZA, 3, ABC)为例：

1. DocNum=3行，Available_SOH=25000，结余=25000-17560=7440，作为DocNum=5行的Available_SOH
2. DocNum=5行，结余=7440-5810=1630，作为DocNum=6行的Available_SOH

预期结果表格 #

尝试的SQL代码（未得到预期结果） #

WITH OrderedData AS (
    SELECT
        DocNum,
        Source,
        Loc,
        Bin,
        Item,
        Required_SOH,
        COALESCE(Available_SOH, 0) AS SOH,
        ROW_NUMBER() OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY DocNum) AS RowNum
    FROM #TempSOH
),
CalculatedSOH AS (
    SELECT
        DocNum,
        Source,
        Loc,
        Bin,
        Item,
        Required_SOH,
        Available_SOH,
        SUM(Available_SOH) OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY RowNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - Required_SOH AS SOH_Calculated
    FROM OrderedData
)
SELECT
    DocNum,
    Source,
    Loc,
    Bin,
    Item,
    Required_SOH,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY DocNum) = 1 THEN SOH_Calculated
        ELSE LAG(SOH_Calculated, 1, 0) OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY DocNum)
    END AS SOH
FROM CalculatedSOH
ORDER BY DocNum;

正确解决方案 #

要实现滚动结转的库存计算，核心是用累计求和结合初始库存推导每行可用库存，修正后的SQL如下：

WITH OrderedData AS (
    -- 按分组排序标记行号，提取每组初始库存
    SELECT
        DocNum,
        Source,
        Loc,
        Bin,
        Item,
        Required_SOH,
        Available_SOH,
        ROW_NUMBER() OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY DocNum) AS RowNum,
        FIRST_VALUE(Available_SOH) OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY DocNum) AS Initial_SOH
    FROM #TempSOH
),
CumulativeRequired AS (
    SELECT
        *,
        -- 计算当前行及之前所有行的累计需求总和
        SUM(Required_SOH) OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY RowNum) AS Cumulative_Required
    FROM OrderedData
)
SELECT
    DocNum,
    Source,
    Loc,
    Bin,
    Item,
    Required_SOH,
    -- 可用库存 = 初始库存 - 上一行累计需求
    CASE
        WHEN RowNum = 1 THEN Initial_SOH
        ELSE Initial_SOH - (Cumulative_Required - Required_SOH)
    END AS Available_SOH
FROM CumulativeRequired
ORDER BY Source, Loc, Bin, Item, DocNum;

代码说明 #

1. OrderedData：按分组排序并标记行号，用FIRST_VALUE提取每组最早DocNum行的初始可用库存。
2. CumulativeRequired：计算每组内从第一行到当前行的累计需求总和。
3. 最终计算：
   • 第一行直接使用初始库存
   • 后续行可用库存 = 初始库存 - 到上一行为止的累计需求总和（当前累计需求减去当前行需求）

此逻辑可准确实现库存滚动结转，优先满足更早DocNum的需求。

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