SQL Server 2019递归CTE实现休假余额预测需求

解决方案 #

以下是基于递归CTE实现的完整SQL方案，涵盖双周计提规则、跨年度结转限制，以及未来6个月和入职周年日的余额计算：

WITH Empl_Policy AS (
    SELECT 
        e.EMP_ID,
        e.LEAVE_BALANCE AS Current_Balance,
        e.Leave_Type_ID,
        e.Hire_date,
        e.Last_Accrual_Date,
        -- 计算当前任职月数
        DATEDIFF(MONTH, e.Hire_date, GETDATE()) AS Months_In_Service,
        -- 匹配对应计提规则：取Months Until不超过任职月数的最高档位
        MAX(lp.Leave_Rate_Per_Every_Accural) AS Leave_Rate,
        MAX(lp.Annual_Max_CarryOver) AS Annual_Max_CarryOver
    FROM Empl_Leave e
    LEFT JOIN Leave_policy lp 
        ON e.Leave_Type_ID = lp.Leave_Type_Id 
        AND lp.[Months Until] <= DATEDIFF(MONTH, e.Hire_date, GETDATE())
    GROUP BY e.EMP_ID, e.LEAVE_BALANCE, e.Leave_Type_ID, e.Hire_date, e.Last_Accrual_Date
),
Accrual_Schedule AS (
    -- 递归起始点：最后计提日的初始状态
    SELECT 
        EMP_ID,
        Current_Balance,
        Last_Accrual_Date AS Accrual_Date,
        DATEADD(WEEK, 2, Last_Accrual_Date) AS Next_Accrual_Date,
        Leave_Rate,
        Annual_Max_CarryOver,
        Hire_date,
        Last_Accrual_Date AS Original_Last_Accrual
    FROM Empl_Policy
    UNION ALL
    -- 递归生成后续双周计提记录
    SELECT 
        a.EMP_ID,
        -- 计算计提后余额，处理跨年度结转限制
        CASE 
            WHEN YEAR(a.Next_Accrual_Date) <> YEAR(a.Accrual_Date) THEN
                LEAST(a.Current_Balance, a.Annual_Max_CarryOver) + ISNULL(a.Leave_Rate, 0)
            ELSE
                a.Current_Balance + ISNULL(a.Leave_Rate, 0)
        END AS Current_Balance,
        a.Next_Accrual_Date AS Accrual_Date,
        DATEADD(WEEK, 2, a.Next_Accrual_Date) AS Next_Accrual_Date,
        a.Leave_Rate,
        a.Annual_Max_CarryOver,
        a.Hire_date,
        a.Original_Last_Accrual
    FROM Accrual_Schedule a
    -- 终止条件：覆盖未来6个月及下一个入职周年日的所有计提
    WHERE a.Next_Accrual_Date <= DATEADD(MONTH, 6, a.Original_Last_Accrual)
       OR a.Next_Accrual_Date <= DATEADD(YEAR, DATEDIFF(YEAR, a.Hire_date, a.Original_Last_Accrual) + 1, a.Hire_date)
)
-- 汇总生成预期输出
SELECT 
    es.EMP_ID AS Empl_ID,
    es.Current_Balance,
    -- 未来6个月的最终余额
    (SELECT TOP 1 Current_Balance 
     FROM Accrual_Schedule a 
     WHERE a.EMP_ID = es.EMP_ID 
       AND a.Accrual_Date <= DATEADD(MONTH, 6, es.Last_Accrual_Date) 
     ORDER BY a.Accrual_Date DESC) AS [For next six Month balance],
    -- 入职周年日的最终余额
    (SELECT TOP 1 Current_Balance 
     FROM Accrual_Schedule a 
     WHERE a.EMP_ID = es.EMP_ID 
       AND a.Accrual_Date <= DATEADD(YEAR, DATEDIFF(YEAR, es.Hire_date, es.Last_Accrual_Date) + 1, es.Hire_date) 
     ORDER BY a.Accrual_Date DESC) AS [For Until Employee Hire Anniversary]
FROM Empl_Policy es
ORDER BY es.EMP_ID;

关键逻辑说明 #

1. Empl_Policy 匹配政策

   • 计算员工当前任职月数，通过LEFT JOIN匹配对应档位的休假政策：取Months Until不超过任职月数的最高规则（例如，任职24个月及以上的员工使用Months Until=24的政策）
   • 对无匹配政策的员工（如EMP_ID 703的LT3），用ISNULL处理计提率，避免计算错误

2. Accrual_Schedule 递归计提

   • 从员工最后计提日开始，每递归一次生成下一个双周计提日期
   • 跨年度时，先将余额截断至Annual_Max_CarryOver上限，再添加本次计提额，严格遵守结转限制
   • 终止条件确保覆盖未来6个月和下一个入职周年日的所有计提节点，避免冗余计算

3. 最终结果汇总

   • 通过子查询筛选出每个员工在目标时间点前的最后一次计提余额，得到未来6个月和入职周年日的预测余额

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