基于EFFECTIVESTARTDATE字段计算DateDiff并更新DaysInPeriod字段

针对SQL Server 2008无法使用LAG()/LEAD()函数的情况，我们可以用递归CTE来实现你需要的分组累计逻辑，完美适配Credit_Line_NO和ASOFDATE的分组重置规则。下面是完整的解决方案：

实现步骤 #

1. 先给每个分组内的记录按EFFECTIVESTARTDATE生成行号，作为递归关联的依据；
2. 用递归CTE初始化分组第一条记录的DaysInPeriod为90，然后逐行累加日期差；
3. 将计算结果更新回原表。

完整SQL代码 #

WITH RankedData AS (
    -- 生成分组内的行号，按EFFECTIVESTARTDATE排序
    SELECT 
        Credit_Line_NO,
        ASOFDATE,
        EFFECTIVESTARTDATE,
        DaysInPeriod,
        -- 按分组排序生成行号，确保同一分组内顺序正确
        ROW_NUMBER() OVER (
            PARTITION BY Credit_Line_NO, ASOFDATE 
            ORDER BY EFFECTIVESTARTDATE -- 如果日期重复，可追加主键字段保证唯一性
        ) AS rn
    FROM Reporting_Table
),
RecursiveDays AS (
    -- 锚点：分组第一条记录，DaysInPeriod设为90
    SELECT 
        Credit_Line_NO,
        ASOFDATE,
        EFFECTIVESTARTDATE,
        CAST(90 AS INT) AS DaysInPeriod,
        rn
    FROM RankedData
    WHERE rn = 1

    UNION ALL

    -- 递归：关联上一行，累加日期差得到当前行的DaysInPeriod
    SELECT 
        rd.Credit_Line_NO,
        rd.ASOFDATE,
        rd.EFFECTIVESTARTDATE,
        -- 上一行的累计天数 + 当前行与上一行的日期差
        CAST(rdp.DaysInPeriod + DATEDIFF(day, rdp.EFFECTIVESTARTDATE, rd.EFFECTIVESTARTDATE) AS INT) AS DaysInPeriod,
        rd.rn
    FROM RankedData rd
    INNER JOIN RecursiveDays rdp 
        ON rd.Credit_Line_NO = rdp.Credit_Line_NO
        AND rd.ASOFDATE = rdp.ASOFDATE
        AND rd.rn = rdp.rn + 1
)
-- 更新原表的DaysInPeriod字段
UPDATE rt
SET rt.DaysInPeriod = rd.DaysInPeriod
FROM Reporting_Table rt
INNER JOIN RecursiveDays rd
    ON rt.Credit_Line_NO = rd.Credit_Line_NO
    AND rt.ASOFDATE = rd.ASOFDATE
    AND rt.EFFECTIVESTARTDATE = rd.EFFECTIVESTARTDATE
-- 如果分组内记录超过100条，取消递归深度限制
OPTION (MAXRECURSION 0);

关键说明 #

• 如果你的需求是每条记录的DaysInPeriod仅为当前行与上一行的日期差（而非累计），只需要把递归成员里的计算逻辑改成CAST(DATEDIFF(day, rdp.EFFECTIVESTARTDATE, rd.EFFECTIVESTARTDATE) AS INT)即可；
• 如果同一分组内存在重复的EFFECTIVESTARTDATE，建议在ROW_NUMBER()的ORDER BY里追加表的主键字段（比如ID），避免行号生成混乱；
• OPTION (MAXRECURSION 0)用于处理分组内记录超过100条的场景，SQL Server默认递归深度是100，取消限制后可以支持任意数量的行。

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