如何使用SQL将日期跨度转换为月度记录

需求概述 #

将用户的多段跨月日期跨度数据，按月份拆分为多行记录，以每月1日作为活跃月份标识；若结束日期为未来日期，则仅拆分至当前年月。

现有数据 #

解决方案 #

通过生成连续月份序列 + 关联原数据筛选有效月份的方式实现，以下是适配需求的SQL代码：

-- 生成足够覆盖需求的连续月份序列（此处生成1000个月，可按需调整）
WITH MonthSequence AS (
    SELECT 
        DATEADD(MONTH, n - 1, '2010-01-01') AS ActiveMonth
    FROM (
        SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM master..spt_values
    ) t
)
-- 关联原数据，筛选每个日期跨度内的有效月份
SELECT 
    t.ID,
    ms.ActiveMonth AS [active month]
FROM 
    YourTableName t
JOIN 
    MonthSequence ms ON 
        ms.ActiveMonth >= DATEFROMPARTS(YEAR(t.[Start date]), MONTH(t.[Start date]), 1)
        AND ms.ActiveMonth <= DATEFROMPARTS(
            YEAR(CASE WHEN t.[end date] > GETDATE() THEN GETDATE() ELSE t.[end date] END),
            MONTH(CASE WHEN t.[end date] > GETDATE() THEN GETDATE() ELSE t.[end date] END),
            1
        )
ORDER BY 
    t.ID, ms.ActiveMonth;

代码说明 #

1. MonthSequence CTE：借助master..spt_values生成连续月份序列，从2010-01-01开始生成1000个月，可根据实际数据范围调整起始日期和生成数量。
2. 关联筛选逻辑：
   • 确保活跃月份不早于日期跨度起始月的1号
   • 若结束日期在未来，自动以当前年月的1号作为拆分上限；否则用原结束日期的当月1号
3. 排序处理：结果按ID和活跃月份升序排列，匹配期望输出格式。

验证结果 #

执行上述SQL后，将得到与需求一致的输出（后续月份会自动生成至当前年月）：

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