如何计算含上一财年期末库存的年度入库累计数量？

需求说明 #

需要计算每个财年的入库数量（InQTY）累计值，同时将上一财年的期末库存（即上一财年最后一条记录的ClosingQTY）加入该累计值中。

解决方案 #

通过以下步骤实现需求：

1. 提取每个物料、每个财年的期末库存（财年最后一条记录的ClosingQTY）
2. 使用窗口函数LAG()获取上一财年的期末库存值
3. 结合财年内的入库累计值，加上上一财年的期末库存得到最终结果

完整SQL代码 #

WITH FYClosing AS (
    -- 获取每个物料+财年的期末库存（财年最后一条记录的ClosingQTY）
    SELECT 
        ItemName,
        FinancialYear,
        ClosingQTY AS FYEndClosingQTY
    FROM (
        SELECT 
            ItemName,
            FinancialYear,
            ClosingQTY,
            -- 标记每个财年的最后一条记录
            ROW_NUMBER() OVER (PARTITION BY ItemName, FinancialYear ORDER BY Date DESC) AS RN
        FROM #tmpTable
    ) t
    WHERE RN = 1
),
BaseDataWithPrevClosing AS (
    -- 关联上一财年的期末库存到当前财年的所有记录
    SELECT 
        a.FinancialYear,
        a.Date,
        a.ItemName,
        a.InQTY,
        a.ClosingQTY,
        -- 计算财年内的入库累计值，NULL的InQTY按0处理
        SUM(ISNULL(a.InQTY, 0)) OVER (PARTITION BY a.ItemName, a.FinancialYear ORDER BY a.Date) AS RunningInQTY,
        -- 获取上一财年的期末库存
        LAG(f.FYEndClosingQTY) OVER (PARTITION BY a.ItemName ORDER BY a.FinancialYear) AS PrevFYClosingQTY
    FROM #tmpTable a
    LEFT JOIN FYClosing f ON a.ItemName = f.ItemName AND a.FinancialYear = f.FinancialYear
)
-- 计算最终的累计值（当前累计入库 + 上一财年期末库存）
SELECT 
    FinancialYear,
    Date,
    ItemName,
    InQTY,
    ClosingQTY,
    RunningInQTY,
    PrevFYClosingQTY,
    -- 上一财年库存为NULL时（即第一个财年），直接取当前累计入库
    ISNULL(RunningInQTY + PrevFYClosingQTY, RunningInQTY) AS RunningTotalWithPrevClosing
FROM BaseDataWithPrevClosing
ORDER BY ItemName, Date;

代码解释 #

• FYClosing CTE：通过ROW_NUMBER()按财年日期倒序排序，筛选出每个物料每个财年的最后一条记录的ClosingQTY，即财年期末库存。
• BaseDataWithPrevClosing CTE：
  • 用SUM(ISNULL(a.InQTY,0))计算财年内的入库累计，处理InQTY为NULL的情况；
  • 用LAG()窗口函数按物料和财年排序，获取上一财年的期末库存值。
• 最终查询：将当前财年的累计入库值加上上一财年的期末库存，第一个财年没有上一财年数据时直接显示累计入库值。

执行结果 #

针对示例数据，执行后会得到如下结果：

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