SQL Server 2019中计算用药覆盖天数及处理用药间隔问题

需求说明 #

• 需计算患者一年内的用药总天数，并确定其用药覆盖区间，属于Gaps and Islands问题变体
• 核心规则：从首次取药日期（DOS）累加用药天数确定覆盖区间；允许7天无用药间隔仍视为连续覆盖
• 治疗达标衡量：患者坚持OUD药物治疗180天以上，且治疗间隔不超过8天

现有尝试与问题 #

尝试使用Preceding窗口函数，但仅能累加相邻记录的天数，无法对整个覆盖分组进行计算。需要实现：将某患者同一用药覆盖区间内的所有处方用药天数，累加到该区间的首次取药日期上。

测试数据与现有SQL（SQL Server 2019环境） #

;WITH TBL AS (
    SELECT CAST('2022-01-24' AS DATE) AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-02-12' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-03-01' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-04-01' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-05-12' AS DOS, 60 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-07-02' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-08-08' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION  
    SELECT '2022-09-24' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION  
    SELECT '2022-10-21' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION  
    SELECT '2022-11-22' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-02-16' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-03-11' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-04-30' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-05-22' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-06-10' AS DOS, 60 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-08-20' AS DOS, 60 AS DAYS, 'Mary' F_NAME
    UNION  
    SELECT '2022-09-24' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION  
    SELECT '2022-10-21' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION  
    SELECT '2022-12-10' AS DOS, 30 AS DAYS, 'Mary' F_NAME
)

SELECT F_NAME, MIN(DOS), MAX(EDOS) , DATEADD(DAY, SUM(DAYS), MIN(DOS))
FROM ( 
    SELECT F_NAME, DOS, EDOS, DAYS, SUM(ADD1) OVER(PARTITION BY F_NAME ORDER BY DOS,EDOS ROWS UNBOUNDED PRECEDING) AS GRP 
    FROM ( 
        SELECT *, DATEADD(DAY, DAYS, DOS) AS EDOS,   
            IIF(DOS <= MAX(DATEADD(DAY, DAYS, DOS)) OVER(PARTITION BY F_NAME ORDER BY DOS, DATEADD(DAY, DAYS, DOS) ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0, 1) AS ADD1 
        FROM TBL 
    ) AS A 
) AS G 
GROUP BY F_NAME, GRP

预期结果 #

• John：仅一个覆盖区间（2022-01-24 至 2022-12-20），全年覆盖且短暂间隔符合7天例外规则
• Mary：两个覆盖区间（2022-02-16 至 2022-04-17、2022-04-30 至 2022-06-12）

优化请求 #

寻求优化上述SQL以实现需求的帮助。

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