合并重叠用药日期范围以统计总用药天数

要解决提前续方导致的重叠日期范围合并问题，核心是将所有重叠或衔接的处方归为同一组，累加组内所有用药天数，最终以组的最早处方日期为起始，加上总天数得到最终结束日期。以下是纯窗口函数实现的方案，无需复杂循环：

完整SQL代码 #

WITH TBL AS (
    SELECT '2023-01-01' AS DOS, 31 AS DAYS, 'A' PERSON
    UNION 
    SELECT '2023-03-01' AS DOS, 31 AS DAYS, 'A' PERSON
    UNION  
    SELECT '2023-04-01' AS DOS, 60 AS DAYS, 'A' PERSON
    UNION  
    SELECT '2023-05-10' AS DOS, 60 AS DAYS, 'A' PERSON
),
-- 计算每个处方的原始结束日期
prescriptions_with_end AS (
    SELECT 
        PERSON,
        DOS,
        DAYS,
        DATEADD(DAY, DAYS, DOS) AS end_date
    FROM TBL
),
-- 生成分组ID：将所有重叠/衔接的处方归为同一组
grouped_prescriptions AS (
    SELECT 
        *,
        SUM(CASE WHEN DOS > COALESCE(LAG(max_end) OVER (PARTITION BY PERSON ORDER BY DOS), '1900-01-01') THEN 1 ELSE 0 END) OVER (PARTITION BY PERSON ORDER BY DOS) AS group_id
    FROM (
        -- 计算到当前处方为止的最大结束日期，用于判断后续处方是否重叠
        SELECT 
            *,
            MAX(end_date) OVER (PARTITION BY PERSON ORDER BY DOS ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_end
        FROM prescriptions_with_end
    ) t
),
-- 聚合分组数据，得到最终合并结果
final_result AS (
    SELECT 
        PERSON,
        MIN(DOS) AS group_start_date,
        SUM(DAYS) AS total_days,
        DATEADD(DAY, SUM(DAYS), MIN(DOS)) AS final_end_date
    FROM grouped_prescriptions
    GROUP BY PERSON, group_id
)
SELECT * FROM final_result;

代码逻辑说明 #

1. prescriptions_with_end：先算出每个处方的原始结束日期（处方日期+用药天数）。
2. grouped_prescriptions：
   • 内层子查询用窗口函数MAX(end_date)计算截至当前处方的所有历史处方的最晚结束日期，以此判断当前处方是否和历史处方重叠。
   • 外层用SUM(CASE...)生成分组ID：如果当前处方的日期晚于历史最晚结束日期（无重叠），则开启新组；否则归为当前组。
3. final_result：对每个分组聚合，取组内最早的处方日期作为起始，累加所有用药天数，最终结束日期为起始日期加上总用药天数。

示例结果 #

针对提供的测试数据，运行后会得到两组结果：

其中第二组的总天数为31+60+60=151，结束日期为2023-03-01加上151天，完全符合需求。

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