多合同无缝衔接场景下SQL覆盖天数计算逻辑修正需求

我编写的SQL用于计算未签订新合同场景下单个合同startdate与enddate的天数差，但当存在新合同无缝衔接旧合同（无时间间隙）的情况时，计算逻辑出错。

以ID为1的记录为例，现有查询计算的是2021-01-01到2021-04-20的天数，而正确计算应该是从首个合同的2015-03-01到最后一个合同的2021-04-20的天数。

当前查询代码 #

WITH t AS (
  SELECT 
    id, 
    enddate, 
    startdate,
    LEAD(startdate) OVER (PARTITION BY id ORDER BY startdate) AS next_startdate
  FROM 
    tab2
),
t_max AS (
  SELECT 
    id, 
    MAX(enddate) AS max_enddate 
  FROM 
    tab2 
  GROUP BY 
    id
)
SELECT 
  tab1.id,
  YEAR(CAST(DATEADD(YEAR, CAST(tab1.year AS INT) - 1900, '19000101') AS DATE)) AS year,
  tab2.startdate,
  tab2.enddate,
  CASE 
    WHEN DATEDIFF(day, tab2.enddate, t.next_startdate) > 1 
       AND tab2.enddate <> '2222-01-01' 
       AND YEAR(tab2.enddate) = YEAR(CAST(DATEADD(YEAR, CAST(tab1.year AS INT) - 1900, '19000101') AS DATE)) THEN tab2.enddate
    WHEN tab2.enddate = t_max.max_enddate 
       AND tab2.enddate <> '2222-01-01' 
       AND YEAR(tab2.enddate) = YEAR(CAST(DATEADD(YEAR, CAST(tab1.year AS INT) - 1900, '19000101') AS DATE)) THEN t_max.max_enddate
    ELSE NULL 
  END AS drop_out,

DATEDIFF(DAY, tab2.startdate, CASE
  WHEN DATEDIFF(day, tab2.enddate, t.next_startdate) > 1
    AND tab2.enddate <> '2222-01-01' 
    AND YEAR(tab2.enddate) = YEAR(CAST(DATEADD(YEAR, CAST(tab1.year AS INT) - 1900, '19000101') AS DATE)) 
    THEN tab2.enddate
  WHEN tab2.enddate = t_max.max_enddate
    AND tab2.enddate <> '2222-01-01' 
    AND YEAR(tab2.enddate) = YEAR(CAST(DATEADD(YEAR, CAST(tab1.year AS INT) - 1900, '19000101') AS DATE)) 
    THEN t_max.max_enddate
  ELSE NULL
END) AS days_insured
  
FROM 
  tab1
JOIN tab2 
  ON tab1.id = tab2.id 
  AND YEAR(tab2.startdate) <= YEAR(CAST(DATEADD(YEAR, CAST(tab1.year AS INT) - 1900, '19000101') AS DATE)) 
  AND YEAR(tab2.enddate) >= YEAR(CAST(DATEADD(YEAR, CAST(tab1.year AS INT) - 1900, '19000101') AS DATE))
LEFT JOIN t 
  ON tab2.id = t.id 
  AND tab2.startdate >= t.startdate
  AND tab2.startdate < t.next_startdate
LEFT JOIN t_max 
  ON tab2.id = t_max.id;

期望输出 #

修正后的SQL #

核心思路是先将无缝衔接的合同合并为连续区间，为每个连续区间标记组ID，再计算每个区间的首次起始日期和末次结束日期，最后关联tab1的年份维度处理输出逻辑。

WITH contract_groups AS (
    -- 为每个ID的合同按时间排序，标记连续区间的组ID
    SELECT 
        id,
        startdate,
        enddate,
        -- 当当前合同的startdate与上一个合同的enddate无缝衔接（间隔<=1天），则属于同一组
        SUM(CASE WHEN DATEDIFF(day, LAG(enddate) OVER (PARTITION BY id ORDER BY startdate), startdate) <= 1 THEN 0 ELSE 1 END) 
            OVER (PARTITION BY id ORDER BY startdate) AS group_id
    FROM tab2
    WHERE enddate <> '2222-01-01' -- 排除未终止的合同
),
group_summary AS (
    -- 计算每个连续区间的首次start、末次end，以及该区间是否有后续合同
    SELECT 
        id,
        group_id,
        MIN(startdate) AS group_start,
        MAX(enddate) AS group_end,
        -- 判断该区间是否是最后一个连续组（无后续合同）
        CASE WHEN MAX(enddate) = (SELECT MAX(enddate) FROM tab2 t WHERE t.id = c.id AND t.enddate <> '2222-01-01') THEN 1 ELSE 0 END AS is_last_group
    FROM contract_groups c
    GROUP BY id, group_id
),
tab1_year AS (
    -- 预处理tab1的年份，转换为日期格式方便后续判断
    SELECT 
        id,
        CAST(DATEADD(YEAR, CAST(year AS INT) - 1900, '19000101') AS DATE) AS year_date,
        YEAR(CAST(DATEADD(YEAR, CAST(year AS INT) - 1900, '19000101') AS DATE)) AS year
    FROM tab1
)
SELECT 
    ty.id,
    ty.year,
    -- 输出该连续区间的首个合同startdate和末次合同enddate
    gs.group_start AS startdate,
    gs.group_end AS enddate,
    -- 仅当该区间的结束年份与当前统计年份一致，且该区间无后续合同时，标记drop_out
    CASE 
        WHEN YEAR(gs.group_end) = ty.year 
             AND (
                 -- 该区间是最后一个连续组，或者下一个区间的start与当前end间隔>1天
                 gs.is_last_group = 1 
                 OR EXISTS (
                     SELECT 1 FROM group_summary gs_next 
                     WHERE gs_next.id = gs.id 
                           AND gs_next.group_id = gs.group_id + 1 
                           AND DATEDIFF(day, gs.group_end, gs_next.group_start) > 1
                 )
             ) THEN gs.group_end
        ELSE NULL 
    END AS drop_out,
    -- 仅当drop_out有值时，计算从区间起始到结束的总天数
    CASE 
        WHEN YEAR(gs.group_end) = ty.year 
             AND (
                 gs.is_last_group = 1 
                 OR EXISTS (
                     SELECT 1 FROM group_summary gs_next 
                     WHERE gs_next.id = gs.id 
                           AND gs_next.group_id = gs.group_id + 1 
                           AND DATEDIFF(day, gs.group_end, gs_next.group_start) > 1
                 )
             ) THEN DATEDIFF(DAY, gs.group_start, gs.group_end)
        ELSE NULL 
    END AS covered_days
FROM tab1_year ty
JOIN group_summary gs 
    ON ty.id = gs.id 
    -- 关联条件：统计年份在区间的起始和结束年份之间
    AND YEAR(gs.group_start) <= ty.year 
    AND YEAR(gs.group_end) >= ty.year
ORDER BY ty.id, ty.year, gs.group_start;

关键修正点 #

1. 连续区间分组：使用LAG(enddate)判断当前合同与上一个合同的时间间隔，将无缝衔接的合同归为同一组，解决了单个合同单独计算的问题。
2. 区间汇总：对每个连续区间取最小startdate和最大enddate，确保计算的是整个连续投保周期的天数。
3. drop_out逻辑优化：仅当区间结束年份与统计年份一致，且该区间无后续衔接合同时，才标记drop_out并计算总天数。

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