出院后后续就诊日期匹配问题：SQL查询逻辑优化需求

需求说明 #

需要为ADMITS表中的每条出院记录匹配VISITS表中对应ID的最早后续就诊日期，但有个关键限制：如果该就诊日期晚于同一ID的下一次出院日期，则将NEXT_VISIT字段设为空。

现有数据（HAVE） #

期望结果（WANT） #

原有代码问题 #

原有SQL仅筛选了就诊日期晚于当前出院日期的记录，但未考虑同一ID的后续出院时间，导致前面的出院记录会匹配到后续出院之后的就诊，不符合需求。原有代码如下：

CREATE TABLE ADMITS
(
ID_NUM INT
,ADMIT_DATE DATE NULL
,DISCHARGE_DATE DATE NULL
)

INSERT INTO ADMITS (ID_NUM, ADMIT_DATE, DISCHARGE_DATE)
VALUES
 (100000301, '4/1/2022', '4/7/2022')
,(100000301, '4/11/2022', '4/18/2022')
,(100000301, '4/18/2022', '5/13/2022')
,(100000005, '8/25/2022', '9/1/2022')
,(100000005, '10/15/2022', '10/22/2022')
,(100000005, '10/22/2022', '11/22/2022');

CREATE TABLE VISITS
(
ID_NUM INT
,SERVICE_DATE DATE NULL
,PROVIDER_ID INT NULL
,SVCOD VARCHAR(10) NULL
)

INSERT INTO VISITS (ID_NUM, SERVICE_DATE, PROVIDER_ID,SVCOD)
VALUES
 (100000301, '5/18/2022', 903263,'T1015')
,(100000301, '5/28/2022', 903263,'T1015')
,(100000301, '11/7/2022', 903263,'T1015')
,(100000301, '11/28/2022', 903263,'T1015')
,(100000005, '9/12/2022', 903263,'T1015')
,(10000005, '10/24/2022', 903263,'T1015')
,(10000005, '11/7/2022', 903263,'T1015')
,(10000005, '11/28/2022', 903263,'T1015');

--This worked for my previous question but not this example
SELECT A.*, (SELECT MIN(SERVICE_DATE) 
             FROM VISITS AS V 
             WHERE V.ID_NUM = A.ID_NUM 
               AND SERVICE_DATE > DISCHARGE_DATE) AS next_visit 
FROM ADMITS AS A

解决方案 #

要实现需求，需先获取每条出院记录的下一次出院日期，再筛选出在当前出院日期和下一次出院日期之间的最早就诊（如果没有下一次出院，则筛选当前出院之后的最早就诊）。

完整实现代码 #

WITH AdmitWithNextDischarge AS (
    SELECT 
        ID_NUM,
        ADMIT_DATE,
        DISCHARGE_DATE,
        -- 获取同一ID的下一次出院日期，按出院时间排序
        LEAD(DISCHARGE_DATE) OVER (PARTITION BY ID_NUM ORDER BY DISCHARGE_DATE) AS NEXT_DISCHARGE
    FROM ADMITS
)
SELECT 
    a.ID_NUM,
    a.ADMIT_DATE,
    a.DISCHARGE_DATE,
    -- 筛选符合条件的最早就诊：晚于当前出院，且早于下一次出院（如果有）
    CASE 
        WHEN EXISTS (SELECT 1 FROM VISITS v WHERE v.ID_NUM = a.ID_NUM AND v.SERVICE_DATE > a.DISCHARGE_DATE) THEN
            (SELECT MIN(v.SERVICE_DATE) 
             FROM VISITS v 
             WHERE v.ID_NUM = a.ID_NUM 
               AND v.SERVICE_DATE > a.DISCHARGE_DATE
               -- 如果有下一次出院，就诊日期必须早于该日期
               AND (a.NEXT_DISCHARGE IS NULL OR v.SERVICE_DATE < a.NEXT_DISCHARGE))
        ELSE NULL
    END AS NEXT_VISIT
FROM AdmitWithNextDischarge a
ORDER BY a.ID_NUM, a.DISCHARGE_DATE;

代码逻辑说明 #

1. CTE部分：使用LEAD()窗口函数，按ID分组、出院日期排序，获取每条记录的下一次出院日期NEXT_DISCHARGE。
2. 主查询部分：
   • 对每条出院记录，先判断是否存在后续就诊。
   • 筛选就诊日期需满足：晚于当前出院日期，并且如果有下一次出院，就诊日期必须早于该下一次出院日期。
   • 取符合条件的最早就诊日期，没有则返回NULL。

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