如何在SQL中实现带过期规则的津贴余额计算逻辑？

核心思路 #

通过递归CTE逐年计算实际可用津贴与余额，同时跟踪需要按规则清除的历史未用余额，严格贴合你提出的津贴规则。

完整SQL代码 #

-- 1. 关联基础表，获取年度周期、固定津贴和已申领数据
WITH BaseData AS (
    SELECT
        p.PeriodID,
        p.StartDate,
        p.EndDate,
        a.OngoingAllowance,
        ISNULL(c.ClaimedAmount, 0) AS ClaimedAmount
    FROM #TmpPeriods p
    LEFT JOIN #TmpAllowance a ON p.PeriodID = a.PeriodID
    LEFT JOIN #TmpClaimed c ON p.PeriodID = c.PeriodID
),
-- 2. 递归计算实际可用津贴和余额，跟踪需清除的历史未用余额
RecursiveAllowance AS (
    -- 初始化第1年数据
    SELECT
        PeriodID,
        StartDate,
        EndDate,
        OngoingAllowance,
        ClaimedAmount,
        CAST(OngoingAllowance AS DECIMAL(10,2)) AS ActualAllowance,
        CAST(MAX(0, OngoingAllowance - ClaimedAmount) AS DECIMAL(10,2)) AS Balance,
        -- 记录第1年产生的未用余额（用于第3年清除）
        CAST(MAX(0, OngoingAllowance - ClaimedAmount) AS DECIMAL(10,2)) AS YearlyUnused
    FROM BaseData
    WHERE PeriodID = 1

    UNION ALL

    -- 递归计算后续年份
    SELECT
        bd.PeriodID,
        bd.StartDate,
        bd.EndDate,
        bd.OngoingAllowance,
        bd.ClaimedAmount,
        -- 实际可用津贴 = 当年固定津贴 + 上年余额 - 需清除的两年前未用余额，上限1000英镑
        CAST(
            LEAST(
                bd.OngoingAllowance + ra.Balance - ISNULL(prev_ra.YearlyUnused, 0),
                1000
            ) AS DECIMAL(10,2)
        ) AS ActualAllowance,
        -- 余额 = 实际可用津贴 - 已申领金额，最低为0
        CAST(
            MAX(
                0,
                LEAST(bd.OngoingAllowance + ra.Balance - ISNULL(prev_ra.YearlyUnused, 0), 1000) - bd.ClaimedAmount
            ) AS DECIMAL(10,2)
        ) AS Balance,
        -- 记录当年产生的未用余额（用于两年后清除）
        CAST(
            MAX(
                0,
                bd.OngoingAllowance - MAX(0, bd.ClaimedAmount - (ra.Balance - ISNULL(prev_ra.YearlyUnused, 0)))
            ) AS DECIMAL(10,2)
        ) AS YearlyUnused
    FROM BaseData bd
    INNER JOIN RecursiveAllowance ra ON bd.PeriodID = ra.PeriodID + 1
    -- 关联两年前的周期，获取需清除的未用余额
    LEFT JOIN RecursiveAllowance prev_ra ON bd.PeriodID = prev_ra.PeriodID + 2
)
-- 3. 输出最终结果
SELECT
    PeriodID AS 年度序号,
    CONVERT(VARCHAR, StartDate, 23) AS 起始日期,
    CONVERT(VARCHAR, EndDate, 23) AS 结束日期,
    OngoingAllowance AS 年度固定津贴_英镑,
    ActualAllowance AS 客户可用津贴_英镑,
    ClaimedAmount AS 已申领金额_英镑,
    Balance AS 余额_英镑
FROM RecursiveAllowance
ORDER BY PeriodID;

逻辑说明 #

1. BaseData CTE：将年度周期表、固定津贴表、已申领表关联，统一基础数据格式，处理ClaimedAmount为NULL的情况（默认0）。
2. 递归初始化（第1年）：
   • 实际可用津贴直接取年度固定津贴500英镑
   • 余额为固定津贴减去已申领金额，最低为0
   • 记录第1年的未用余额，用于第3年清除
3. 递归计算（第2年及以后）：
   • 实际可用津贴：按照规则，当年固定津贴+上年余额，减去两年前的未用余额（如第3年减第1年、第4年减第2年），结果不超过1000英镑上限
   • 余额：实际可用津贴减去已申领金额，最低为0（避免负数）
   • YearlyUnused：记录当年产生的未用余额（仅包含当年固定津贴未使用的部分），用于后续年份清除

边界处理 #

• 已申领金额超过可用津贴时，余额强制设为0
• 自动处理未提交申领记录的年份（默认已申领金额为0）
• 严格控制可用津贴上限不超过1000英镑

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