如何仅针对指定年份（2022）按月份拆分会员周期？

需要将会员的时间周期按月拆分，但仅保留2022年范围内的拆分结果，原代码会拆分所有时间区间，现调整代码实现仅处理目标年份的相关周期。运行环境为SQL Server 2017。

1. 准备测试数据与计数表 #

-- 创建计数表#numbers，用于生成月份偏移量
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS n     
INTO #numbers     
FROM master.dbo.spt_values;

-- 创建测试数据临时表#t
DROP TABLE IF EXISTS #t;
SELECT * INTO #t FROM (    
    SELECT 100 ID, CAST('2022-03-02' AS DATE) eStart, CAST('2022-03-15' AS DATE) eEnd, '1 Same Month island' note, 100 Idx
    UNION SELECT 200, '2022-01-11', '2022-03-27', '2 Same Month Start 1', 2001
    UNION SELECT 200, '2022-02-15', '2022-05-15', '2 Same Month Start 2', 2002
    UNION SELECT 300, '2021-05-08', '2024-12-31', '3 Future eEnd ', 3000
    UNION SELECT 440, '2022-10-15', '2023-10-10', '4 Future end 2', 4400 
) b;

2. 修改后的核心查询代码 #

SELECT DISTINCT 
    t.ID,
    -- 计算拆分后的周期起始日期，仅保留2022年范围内的部分
    CASE 
        WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart) + n.n, 0) < '2022-01-01' THEN NULL
        WHEN t.eStart > '2022-12-31' THEN NULL
        ELSE 
            CASE 
                WHEN n.n = 0 THEN t.eStart
                ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart) + n.n, 0)
            END
    END AS eStart,
    -- 计算拆分后的周期结束日期，仅保留2022年范围内的部分
    CASE 
        WHEN EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart) + n.n, 0)) > '2022-12-31' THEN '2022-12-31'
        WHEN n.n = DATEDIFF(MONTH, t.eStart, t.eEnd) THEN t.eEnd
        ELSE EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart) + n.n, 0))
    END AS eEnd
FROM #t t
JOIN #numbers n 
    ON n.n <= DATEDIFF(MONTH, t.eStart, t.eEnd)
    -- 过滤仅处理与2022年有重叠的周期
    AND (t.eEnd >= '2022-01-01' AND t.eStart <= '2022-12-31')
-- 过滤掉非2022年的拆分记录
WHERE 
    CASE 
        WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart) + n.n, 0) < '2022-01-01' THEN NULL
        WHEN t.eStart > '2022-12-31' THEN NULL
        ELSE 
            CASE 
                WHEN n.n = 0 THEN t.eStart
                ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart) + n.n, 0)
            END
    END IS NOT NULL
ORDER BY t.ID, eStart;

3. 关键修改说明 #

• 过滤目标周期：在JOIN条件中添加AND (t.eEnd >= '2022-01-01' AND t.eStart <= '2022-12-31')，直接排除完全不涉及2022年的会员周期。
• 截断日期范围：计算拆分后的起止日期时，将超出2022年的部分做截断处理：
  • 起始日期早于2022年1月1日的标记为NULL，后续通过WHERE子句过滤
  • 结束日期晚于2022年12月31日的，直接替换为2022-12-31
• 清理无效记录：通过WHERE子句剔除起始日期为NULL的非2022年拆分结果，确保最终输出仅包含目标年份的月度数据。

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