如何修复T-SQL中带56小时上限的滚动累计病假时长查询？

我司病假政策规定累计时长不得超过56小时，编写了如下T-SQL查询实现该逻辑：

SELECT 
    t.employeecode, t.LeaveAccrued, PPE, RecordDate,
    (CASE 
         WHEN (SUM(t.LeaveAccrued) OVER (PARTITION BY t.Employeecode ORDER BY RecordDate)) > 56 
             THEN 56
         WHEN (SUM(t.LeaveAccrued) OVER (PARTITION BY t.Employeecode ORDER BY RecordDate)) <= 56 
             THEN SUM(t.LeaveAccrued) OVER (PARTITION BY t.Employeecode ORDER BY RecordDate)
     END) AS accrued_time
FROM 
    time t

该查询多数场景有效，但员工累计时长达56小时上限后请假时计算出错。例如休29小时病假后，累计时长应降至约27小时，但查询结果未正确计算，示例结果如下：

目前采用年度计算并将滚存数据存入其他表的临时方案，希望彻底修复该查询。

原查询的核心问题是：普通SUM() OVER()窗口函数仅计算从第一条到当前行的原始累计值，未考虑之前触发上限后实际应保留的有效累计时长。要实现“累计不超56小时，请假时从有效累计中扣除”的逻辑，需用递归CTE逐行计算，因为每一行的结果依赖于上一行的最终累计值。

修复后的查询如下：

WITH OrderedRecords AS (
    -- 按员工分组、日期排序生成行号，确保处理顺序正确
    SELECT 
        employeecode, LeaveAccrued, PPE, RecordDate,
        ROW_NUMBER() OVER (PARTITION BY employeecode ORDER BY RecordDate) AS rn
    FROM time t
),
RecursiveAccrual AS (
    -- 递归起点：处理每个员工的第一条记录
    SELECT 
        employeecode, LeaveAccrued, PPE, RecordDate, rn,
        CASE WHEN LeaveAccrued > 56 THEN 56 ELSE LeaveAccrued END AS accrued_time
    FROM OrderedRecords
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续记录：基于上一行的累计值计算当前行结果
    SELECT 
        o.employeecode, o.LeaveAccrued, o.PPE, o.RecordDate, o.rn,
        CASE 
            WHEN r.accrued_time + o.LeaveAccrued > 56 THEN 56  -- 不超过上限
            WHEN r.accrued_time + o.LeaveAccrued < 0 THEN 0    -- 避免负累计（可根据业务调整）
            ELSE r.accrued_time + o.LeaveAccrued
        END AS accrued_time
    FROM OrderedRecords o
    JOIN RecursiveAccrual r 
        ON o.employeecode = r.employeecode 
        AND o.rn = r.rn + 1
)
-- 输出最终结果，保持顺序
SELECT 
    employeecode, LeaveAccrued, PPE, RecordDate, accrued_time
FROM RecursiveAccrual
ORDER BY employeecode, RecordDate;

关键逻辑说明 #

1. OrderedRecords：给每个员工的记录按日期排序并生成行号，保证递归时按时间顺序处理每一条记录。
2. RecursiveAccrual：
   • 起始分支处理第一条记录，直接计算初始累计值，超过56则取56。
   • 递归分支逐行计算：用上一行的有效累计值加上当前行的LeaveAccrued，再根据规则限制在0到56之间（若业务允许负累计，可移除<0 THEN 0的判断）。
3. 最终结果按员工和日期排序，确保输出顺序符合时间线。

示例场景验证 #

针对你提到的休29小时病假的场景：

• 上一行累计为54.8169，加上-29后得到25.8169，该值在0-56范围内，因此当前行accrued_time会正确显示为25.8169，后续记录将基于此值继续计算，解决原查询的错误。

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