如何基于其他行日期为条目填充日期（适配SQL Server 2016）

原表结构 #

需求说明 #

为每个Item填充连续日期：

• Item A：2023-01-01 至 2023-01-24 期间Value为10，2023-01-25 至当前日期Value为20
• Item B：2023-01-01 至 2023-02-28 期间Value为100，2023-03-01 至当前日期Value为200

由于SQL Server 2016不支持GENERATE_SERIES，可以用数字辅助表结合窗口函数实现。

实现代码与说明 #

完整SQL代码 #

WITH Numbers AS (
    -- 生成足够覆盖需求的连续数字（这里取0到3650，覆盖10年日期范围）
    SELECT TOP (3650) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num
    FROM sys.all_columns c1
    CROSS JOIN sys.all_columns c2
),
ItemDateRanges AS (
    -- 为每个Item计算生效日期区间
    SELECT 
        Category,
        Item,
        Value,
        CAST(Date AS DATE) AS StartDate,
        -- 用LEAD获取下一条记录的日期，当前区间结束日为下一条生效日的前一天，最后一条区间结束日设为当前日期
        ISNULL(
            DATEADD(DAY, -1, LEAD(CAST(Date AS DATE)) OVER (PARTITION BY Item ORDER BY CAST(Date AS DATE))),
            CAST(GETDATE() AS DATE)
        ) AS EndDate
    FROM YourTableName -- 替换为你的实际表名
)
-- 关联数字表生成连续日期
SELECT 
    DATEADD(DAY, n.Num, dr.StartDate) AS Date,
    dr.Category,
    dr.Item,
    dr.Value
FROM ItemDateRanges dr
JOIN Numbers n ON DATEADD(DAY, n.Num, dr.StartDate) <= dr.EndDate
ORDER BY dr.Item, Date;

关键逻辑说明 #

1. 数字辅助表：通过sys.all_columns交叉连接生成连续数字，无需创建实体表，可根据需求调整TOP值扩展日期覆盖范围。
2. 日期区间计算：用LEAD窗口函数获取每个Item下一条生效日期，通过DATEADD调整得到当前Value的生效结束日；最后一条记录的结束日自动设为当前日期。
3. 生成连续日期：将数字表与日期区间表关联，为每个区间生成所有连续日期，确保无日期断层。

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