使用CTE将两列日期区间拆分为每月一行的技术问询

需求说明 #

将单条数据中EFF_DATE到TERM_DATE的日期区间拆分为多行，每个月份对应一行记录。

输入数据示例 #

期望输出示例 #

当前简化查询语句 #

SELECT 
    Name,
    [MemberID],
    [EFF_DATE], [TERM_DATE]
FROM 
    MYTABLE t
WHERE
    pat_id = '12121212' AND rn = 1

解决方案 #

你需要将原查询作为基础数据源，结合递归CTE生成月份序列，替换原示例中按天拆分的逻辑为按月拆分。以下是整合后的完整SQL语句：

WITH MonthRange_CTE AS (
    -- 初始化：提取目标数据，并将EFF_DATE转为对应月份的第一天
    SELECT 
        Name,
        MemberID,
        DATEFROMPARTS(YEAR(EFF_DATE), MONTH(EFF_DATE), 1) AS MonthStart,
        TERM_DATE
    FROM MYTABLE t
    WHERE pat_id = '12121212' AND rn = 1

    UNION ALL

    -- 递归生成下一个月的第一天，直到超过TERM_DATE所在月份
    SELECT 
        Name,
        MemberID,
        DATEADD(MONTH, 1, MonthStart),
        TERM_DATE
    FROM MonthRange_CTE
    WHERE MonthStart < DATEFROMPARTS(YEAR(TERM_DATE), MONTH(TERM_DATE), 1)
)
SELECT 
    Name,
    MemberID,
    YEAR(MonthStart) AS yearnumb,
    MONTH(MonthStart) AS monthnumb,
    MonthStart AS memberall
FROM MonthRange_CTE
ORDER BY MonthStart;

代码说明 #

1. CTE初始化：从你的原查询中获取数据，同时将EFF_DATE转换为当月第一天，作为月份序列的起始点。
2. 递归逻辑：每次给当前月份起始日期加1个月，生成下一个月的第一天，直到生成的月份超过TERM_DATE所在月份为止。
3. 最终输出：从CTE中提取字段，格式化年份、月份，得到期望的结果结构。

如果使用SQL Server 2022及以上版本，还可以用GENERATE_SERIES替代递归CTE，写法更简洁：

SELECT 
    t.Name,
    t.MemberID,
    YEAR(month_start) AS yearnumb,
    MONTH(month_start) AS monthnumb,
    month_start AS memberall
FROM MYTABLE t
CROSS APPLY (
    SELECT DATEADD(MONTH, n, DATEFROMPARTS(YEAR(t.EFF_DATE), MONTH(t.EFF_DATE), 1)) AS month_start
    FROM GENERATE_SERIES(0, DATEDIFF(MONTH, t.EFF_DATE, t.TERM_DATE)) n
) months
WHERE t.pat_id = '12121212' AND t.rn = 1
ORDER BY month_start;

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