SQL中识别同类型记录日期间隔并合并连续记录

• 当同一ClaimID、同一dim_legalstat_key的承诺记录存在时间间隔时，事实表需保留多条记录，展示每段连续承诺的准确起止日期（例如ClaimID 1001的情况）；
• 当同一ClaimID、同一dim_legalstat_key的承诺记录**无时间间隔（含重叠、日期衔接）**时，需合并为单条记录，取最早的起始日期、最晚的结束日期，并重新计算承诺天数。

测试数据 #

CREATE TABLE #legal_data (
    ClaimID VARCHAR(20)
    ,dim_legalstat_key int -- 维度键
    ,[order_start_date] DATE
    ,[order_end_date] DATE
    ,[days_committed]  int -- order_start_date到order_end_date的天数差
)

INSERT INTO #legal_data
VALUES
    ('1001','11','2022-05-11','2022-10-29','171')
    ,('1001','131','2022-07-15','2023-03-19','247')
    ,('1001','116','2023-03-14','2023-03-20','6')
    ,('1001','11','2023-03-20','2023-03-23','3')
    ,('1207','11','2022-09-13','2023-03-12','180')
    ,('1207','11','2023-03-10','2023-03-23','13')
    ,('1924','2','2021-12-18','2022-06-19','183')
    ,('1924','2','2022-06-19','2023-12-20','184')
    ,('1842','77','2021-02-20','2022-06-17','482')
    ,('1842','77','2022-06-18','2023-12-20','550')
    ,('1661','22','2022-02-14','2023-03-20','399')
    ,('1661','22','2022-02-14','2023-03-23','402')
    ,('1553','4','2022-01-14','2022-02-12','29')
    ,('1553','4','2022-02-14','2023-03-23','402')

期望结果 #

CREATE TABLE #legal_Result (
    ClaimID VARCHAR(20)
    ,dim_legalstat_key int-- 维度键
    ,[order_start_date] DATE
    ,[order_end_date] DATE
    ,[days_committed]  int -- order_start_date到order_end_date的天数差
)

INSERT INTO #legal_Result
VALUES
    ('1001','11','2022-05-11','2022-10-29','171')
    ,('1001','131','2022-07-15','2023-03-19','247')
    ,('1001','116','2023-03-14','2023-03-20','6')
    ,('1001','11','2023-03-20','2023-03-23','3')
    ,('1207','11','2022-09-13','2023-03-23','191')
    ,('1924','2','2021-12-18','2023-12-20','732')
    ,('1842','77','2021-02-20','2023-12-20','1033') -- 日期衔接，合并
    ,('1661','22','2022-02-14','2023-03-23','402') -- 区间重叠，合并
    ,('1553','4','2022-01-14','2022-02-12','29') -- 存在时间间隔，保留原记录
    ,('1553','4','2022-02-14','2023-03-23','402')

解决方案SQL #

WITH ranked_data AS (
    -- 按ClaimID和维度键分组，按起始日期排序，标记连续区间
    SELECT 
        ClaimID,
        dim_legalstat_key,
        order_start_date,
        order_end_date,
        -- 判断当前记录是否与上一条记录连续（含重叠、衔接），生成分组ID
        SUM(CASE WHEN order_start_date <= DATEADD(day, 1, LAG(order_end_date) OVER (PARTITION BY ClaimID, dim_legalstat_key ORDER BY order_start_date)) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ClaimID, dim_legalstat_key ORDER BY order_start_date) AS group_id
    FROM #legal_data
)
-- 按分组聚合，生成最终结果
SELECT 
    ClaimID,
    dim_legalstat_key,
    MIN(order_start_date) AS order_start_date,
    MAX(order_end_date) AS order_end_date,
    DATEDIFF(day, MIN(order_start_date), MAX(order_end_date)) AS days_committed
FROM ranked_data
GROUP BY ClaimID, dim_legalstat_key, group_id
ORDER BY ClaimID, order_start_date;

逻辑说明 #

1. ranked_data CTE：
   • 按ClaimID和dim_legalstat_key分组，对每组内的记录按order_start_date排序；
   • 使用LAG函数获取上一条记录的结束日期，判断当前记录的起始日期是否与上一条区间连续（order_start_date <= 上一条结束日期 + 1，涵盖重叠、次日衔接的场景）；
   • 通过累加判断结果生成group_id，同一连续区间的记录会被分配相同的分组ID。
2. 聚合查询：
   • 按ClaimID、dim_legalstat_key和group_id分组，取每组内最早的起始日期、最晚的结束日期；
   • 用DATEDIFF重新计算合并后的承诺天数，确保结果准确。

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