SQL查询期初余额始终为0，未继承上月期末余额问题排查

执行的SQL语句 #

SELECT 
    DATEPART(YEAR, PostingDate) AS year, 
    DATEPART(MONTH, PostingDate) AS month, 
    SUM(CASE WHEN G_L_EntryNo = 1 THEN Amount ELSE 0 END) + 
       SUM(CASE WHEN G_L_EntryNo > 1 AND PostingDate < DATEFROMPARTS(DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate), 1) THEN Amount ELSE 0 END) AS opening_balance,
    SUM(CASE WHEN G_L_EntryNo > 1 AND PostingDate >= DATEFROMPARTS(DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate), 1) THEN Amount ELSE 0 END) AS change
FROM 
    tblG_L_Entry
WHERE 
    G_L_AccountNo = '1010000'
GROUP BY 
    DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate)
ORDER BY 
    DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate);

查询结果 #

year    month   opening_balance change
---------------------------------------
2021    8   0.000   -15.000
2021    9   0.000   -5250.000
2021    10  0.000   -588.000
2021    11  0.000   -1141.980
2021    12  0.000   -2174.000
2022    1   0.000   -210.000
2022    2   0.000   -340.000
2022    3   0.000   -1560.000

注：变动额(change)为负数属于实际业务数据，无需关注。当前问题为：各月期初余额(opening_balance)始终显示为0，未将上月期末余额作为本月期初余额，请问查询语句哪里出错了？

错误原因分析 #

你的opening_balance计算逻辑存在核心问题：

• 语句中PostingDate < DATEFROMPARTS(DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate), 1)这个条件，在按年+月分组后，当前分组内的所有PostingDate都属于该年月，不可能小于当月1号，因此这部分SUM的结果永远为0。
• 加上G_L_EntryNo = 1的条目可能不存在或无数据，最终导致所有月份的期初余额都为0。
• 本质错误：你试图在当前年月分组内计算期初余额，但期初余额是截至上月的所有变动累计值，需要跨分组统计。

修正后的SQL语句 #

WITH monthly_changes AS (
    SELECT 
        DATEPART(YEAR, PostingDate) AS year, 
        DATEPART(MONTH, PostingDate) AS month,
        SUM(Amount) AS change
    FROM tblG_L_Entry
    WHERE G_L_AccountNo = '1010000'
    GROUP BY DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate)
)
SELECT 
    year,
    month,
    -- 期初余额 = 截至上月的所有变动累计值，第一个月无上月数据则为0
    COALESCE(SUM(change) OVER(ORDER BY year, month ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS opening_balance,
    change
FROM monthly_changes
ORDER BY year, month;

修正逻辑说明 #

1. 先用CTEmonthly_changes计算每个月的总变动额（简化了你原有的change计算逻辑，原逻辑本质就是统计当月所有条目的Amount总和）。
2. 使用窗口函数SUM(change) OVER(...)计算从第一个月到当前月的前一个月的累计变动总和，这个值就是当月的期初余额（上月期末余额=截至上月的所有变动累计）。
3. 用COALESCE处理第一个月的特殊情况：第一个月没有上月数据，所以期初余额设为0，和你原结果的初始状态一致。

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