如何在T-SQL中处理处方日期区间重叠，计算有效用药总天数

我正在做处方用药行为分析，核心需求是判断患者在90天季度内（比如2022-04-01至2022-06-30）是否服用某类药物满60天。目前已经能算出每张处方在季度范围内的有效天数，但遇到一个问题：同一药物类别常有多张处方（比如患者换同类别其他药物），直接累加总天数的话，日期重叠的部分会被重复计算，这明显不合理。

示例数据 #

• 患者1的有效天数63天，符合要求，应纳入统计；
• 患者2的两张处方无重叠，总有效天数65天，符合要求；
• 患者3的两张处方完全重叠，实际有效天数仅32天，不符合要求。

必须在T-SQL中实现日期重叠检查，只累加非重叠部分的天数。

T-SQL实现代码 #

以下代码将原基础数据查询作为CTE，通过窗口函数合并同一患者同一药物类别的重叠/相邻日期区间，最终计算实际非重叠的总有效天数：

DECLARE @startdt DATE;
DECLARE @enddt DATE;
SET @startdt = '2022-04-01';
SET @enddt = '2022-06-30';

WITH RxQuarterIntervals AS (
    -- 获取患者、药物类别及处方在季度内的实际起止日期
    SELECT DISTINCT
        rx.patid,
        d.medication_category AS medcat,
        -- 修正开始日期：取季度开始和处方开始的较大值
        CASE WHEN rx.start_date < @startdt THEN @startdt ELSE rx.start_date END AS interval_start,
        -- 修正结束日期：取季度结束和处方结束的较小值
        CASE WHEN rx.end_date > @enddt THEN @enddt ELSE rx.end_date END AS interval_end
    FROM rx
    INNER JOIN Drug_names_categories d
        ON rx.drugname = d.drugname
    WHERE rx.start_date < '2022-07-01' 
      AND rx.end_date > '2022-03-30'
      AND rx.patid IS NOT NULL
      AND d.medication_category IS NOT NULL
      AND d.medication_category <> ''
),
-- 标记重叠区间分组
MergedIntervals AS (
    SELECT
        patid,
        medcat,
        interval_start,
        interval_end,
        -- 当前区间开始日期大于上一个区间结束日期时，新建分组
        SUM(CASE WHEN interval_start > COALESCE(LAG(interval_end) OVER (PARTITION BY patid, medcat ORDER BY interval_start), '1900-01-01') THEN 1 ELSE 0 END) OVER (PARTITION BY patid, medcat ORDER BY interval_start) AS group_id
    FROM RxQuarterIntervals
),
-- 计算每个合并后区间的天数
IntervalDays AS (
    SELECT
        patid,
        medcat,
        DATEDIFF(DAY, MIN(interval_start), MAX(interval_end)) + 1 AS merged_days
    FROM MergedIntervals
    GROUP BY patid, medcat, group_id
)
-- 最终统计总非重叠天数并判断是否达标
SELECT
    patid,
    medcat,
    SUM(merged_days) AS total_non_overlap_days,
    CASE WHEN SUM(merged_days) >= 60 THEN '达标' ELSE '未达标' END AS eligibility_status
FROM IntervalDays
GROUP BY patid, medcat
ORDER BY patid, medcat;

代码说明 #

1. RxQuarterIntervals：先修正处方起止日期，确保只保留季度范围内的区间，避免计算超出季度边界的天数。
2. MergedIntervals：使用LAG()窗口函数追踪上一个区间的结束日期，标记出不重叠的区间分组。
3. IntervalDays：对每个分组合并区间，计算该合并区间的实际天数。
4. 最后汇总每个患者每个药物类别的总天数，并直接判断是否满足60天的要求。

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