能否一步拆分月度会员数据：保留完整与非完整月度分段？

我需要将月度会员数据拆分为单个完整月度，但存在多种非完整月度分段的情况，导致需求变复杂。想知道能不能一步实现需求（不用先把输入拆成完整/非完整月度）？我试过修改eStart/eEnd日期，但希望保留输入数据原样。下面是自包含脚本（含数据准备、输入及预期输出），当前代码只处理完整月度，能不能同时覆盖首尾的非完整分段？

--- SQL Server 2019
SELECT DISTINCT t.*, '--' f, d.*
FROM #t t
JOIN #date_dim d     ON    d.CalDate  BETWEEN eStart AND eEnd
    AND d.dd = 1  
JOIN #date_dim d2    ON    d2.CalDate BETWEEN eStart AND eEnd
    AND d2.dd = d2.mm_Last_DD 

/*  -----    data prep part
SELECT * INTO #t  FROM (    --   DROP TABLE IF EXISTS  #t
          SELECT 100 ID,  CAST('2022-03-02' AS DATE) eStart ,  CAST('2022-03-15' AS DATE) eEnd, '1 Same Month island' note  
    UNION SELECT 200,  '2022-03-01' ,   '2022-03-27', '2 Same Month Start'
    UNION SELECT 300,  '2022-03-08' ,   '2022-03-31', '3 Same Month End' 

    UNION SELECT 440,  '2022-01-15' ,   '2022-02-28', '4 Diff Month End'  
    UNION SELECT 550,  '2022-03-08' ,   '2022-05-10', '5 Diff Month Island'  
    UNION SELECT 660,  '2022-03-1'  ,   '2022-6-15',  '6 Diff Month Start' 
) b      --    SELECT * FROM #t

;WITH cte AS (    --DROP TABLE IF EXISTS  #date_dim
  SELECT TOP 180  
  CAST('1/1/2022' AS DATETIME) + ROW_NUMBER() OVER(ORDER BY  number) CalDate 
  FROM  master..spt_values  )

  SELECT CalDate
  , MONTH(Caldate)  MM,   DATEADD(dd, -( DAY( Caldate ) -1 ), Caldate) MM_start,  EOMONTH(Caldate) MM_End, day(Caldate) dd, DAY(EOMONTH(Caldate)) mm_Last_DD
, CONVERT(nvarchar(6), Caldate, 112) YYYYMM, YEAR(CalDate) YYYY 
  ,CASE WHEN CalDate =  EOMONTH(Caldate) THEN 'Y' ELSE 'N' END month_End_YN
  INTO #date_dim       ----         SELECT * FROM #date_dim
  FROM cte

*/

解决方案 #

可以通过关联日期维度表获取会员周期覆盖的所有月份，再针对每个月份计算实际的分段日期（取原始周期和当月区间的交集），同时保留原始的eStart和eEnd数据。修改后的查询如下：

--- SQL Server 2019
SELECT 
    t.*,
    -- 计算当前分段的实际起始日期（取原始eStart和当月月初的较大值）
    CASE 
        WHEN t.eStart > d.MM_start THEN t.eStart 
        ELSE d.MM_start 
    END AS segment_start,
    -- 计算当前分段的实际结束日期（取原始eEnd和当月月末的较小值）
    CASE 
        WHEN t.eEnd < d.MM_End THEN t.eEnd 
        ELSE d.MM_End 
    END AS segment_end,
    d.YYYYMM,
    d.MM_start AS month_start,
    d.MM_End AS month_end
FROM #t t
-- 关联日期维度表，获取会员周期覆盖的所有月份的月初记录
JOIN #date_dim d 
    ON d.MM_start BETWEEN DATEADD(dd, -(DAY(t.eStart)-1), t.eStart) -- 原始周期起始月的月初
                      AND DATEADD(dd, -(DAY(t.eEnd)-1), t.eEnd)     -- 原始周期结束月的月初
    AND d.dd = 1 -- 只取每月第一天的记录来代表整个月份
ORDER BY t.ID, d.YYYYMM

逻辑说明 #

1. 关联日期维度表时，通过MM_start（当月月初）匹配会员周期覆盖的所有月份，范围是原始周期起始月到结束月的所有月初。
2. 对每个匹配到的月份，计算实际分段日期：
   • segment_start：若会员周期起始日期晚于当月月初，用会员的eStart，否则用当月月初。
   • segment_end：若会员周期结束日期早于当月月末，用会员的eEnd，否则用当月月末。
3. 全程保留原始的t.*数据，完全满足“不修改输入数据原样”的要求。

执行后即可得到包含所有完整月度、首尾非完整分段的结果，与预期输出一致。

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