T-SQL实现各ID最大TargetDate至当前日期的周度目标日期行生成

原始数据 #

需求说明 #

为每个ID提取最大的TargetDate，生成该日期至GETDATE()之间每一周的新行，补充到现有数据中（预期输出中加粗行为新增内容）。

尝试的错误代码 #

;WITH cte AS 
(
SELECT [Id], [EntryDate], MAX([Target Day]) as [TargetDate]
FROM [Base_List]
GROUP BY  [Id], [EntryDate]

UNION ALL

SELECT cte.[Id], x.[EntryDate], CAST(DATEADD(week,1,[TargetDate]) as date)
FROM cte

INNER JOIN [Base_List] as x
    ON x.[Id] = cte.[Id]
    AND [TargetDate] < GETDATE()

)

SELECT [Id], [EntryDate], [TargetDate]
FROM cte
OPTION (MAXRECURSION 0)

错误原因分析 #

1. 递归部分不必要关联Base_List表，会导致重复生成行
2. 递归终止条件判断位置错误，应直接在递归成员中限制生成日期不超过GETDATE()
3. 字段名错误：MAX([Target Day])应为MAX([TargetDate])

正确实现代码 #

-- 先获取每个ID的最大TargetDate和对应EntryDate
WITH MaxTargets AS (
    SELECT 
        Id, 
        EntryDate, 
        MAX(TargetDate) AS MaxTargetDate
    FROM Base_List
    GROUP BY Id, EntryDate
),
-- 递归生成每周的日期行
RecursiveWeeks AS (
    SELECT 
        Id, 
        EntryDate, 
        MaxTargetDate AS TargetDate
    FROM MaxTargets

    UNION ALL

    SELECT 
        r.Id, 
        r.EntryDate, 
        DATEADD(WEEK, 1, r.TargetDate) AS TargetDate
    FROM RecursiveWeeks r
    WHERE DATEADD(WEEK, 1, r.TargetDate) <= CAST(GETDATE() AS DATE)
)
-- 合并原始数据与新增递归行
SELECT Id, EntryDate, TargetDate FROM Base_List
UNION ALL
SELECT Id, EntryDate, TargetDate FROM RecursiveWeeks
-- 可选：按ID和日期排序方便查看
ORDER BY Id, TargetDate;

预期输出示例（假设GETDATE()为2022-10-12） #

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