SQL Server中如何精准查询出院后的下一次就诊日期

在SQL Server中查询患者出院后的下一次就诊日期时，当前查询仅返回首次出院（2022年9月1日）后的2022年9月12日就诊日期，所有出院记录的next_visit字段均为此值。期望实现：2022年9月1日出院对应9月12日就诊，2022年10月22日出院无匹配就诊，2022年11月22日出院对应11月28日就诊。

尝试过将ADMITS表与VISITS表关联，筛选VISITS.SERVICE_DATE大于ADMITS.DISCHARGE_DATE的记录，再取SERVICE_DATE的最小值，但得到错误结果。

已有结果 #

期望结果 #

表结构及测试数据 #

ADMITS表 #

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

INSERT INTO ADMITS (ID_NUM, ADMIT_DATE, DISCHARGE_DATE)
VALUES
 (100000005, '8/25/2022', '9/1/2022')
,(100000005, '10/15/2022', '10/22/2022')
,(100000005, '10/22/2022', '11/22/2022');

VISITS表 #

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
 (100000005, '9/12/2022', 903263,'T1015')
,(100000005, '10/24/2022', 903263,'T1015')
,(100000005, '11/7/2022', 903263,'T1015')
,(100000005, '11/28/2022', 903263,'T1015');

解决方案 #

方法1：使用CROSS APPLY关联最近的就诊记录 #

针对每条出院记录，筛选出该患者在出院日期之后、且在下一次入院日期之前的最早就诊记录，若无符合条件的记录则返回NULL。

SELECT 
    a.ID_NUM,
    a.ADMIT_DATE,
    a.DISCHARGE_DATE,
    v.SERVICE_DATE AS next_visit
FROM ADMITS a
CROSS APPLY (
    SELECT TOP 1 SERVICE_DATE
    FROM VISITS v
    WHERE v.ID_NUM = a.ID_NUM
      AND v.SERVICE_DATE > a.DISCHARGE_DATE
      -- 过滤掉下一次入院前的就诊记录
      AND v.SERVICE_DATE < COALESCE(
          (SELECT MIN(ADMIT_DATE) FROM ADMITS a2 WHERE a2.ID_NUM = a.ID_NUM AND a2.ADMIT_DATE > a.DISCHARGE_DATE),
          '9999-12-31'
      )
    ORDER BY SERVICE_DATE ASC
) v
ORDER BY a.DISCHARGE_DATE;

方法2：使用窗口函数标记对应关系 #

合并出院和就诊事件，通过窗口函数找到每条出院记录对应的下一次有效就诊：

WITH AllEvents AS (
    SELECT 
        ID_NUM,
        EVENT_DATE = DISCHARGE_DATE,
        EVENT_TYPE = 'DISCHARGE',
        ADMIT_DATE
    FROM ADMITS
    UNION ALL
    SELECT 
        ID_NUM,
        EVENT_DATE = SERVICE_DATE,
        EVENT_TYPE = 'VISIT',
        NULL AS ADMIT_DATE
    FROM VISITS
),
RankedEvents AS (
    SELECT 
        *,
        -- 获取当前记录之后的第一个就诊日期
        NEXT_VISIT = LEAD(CASE WHEN EVENT_TYPE = 'VISIT' THEN EVENT_DATE END) OVER (
            PARTITION BY ID_NUM 
            ORDER BY EVENT_DATE ASC
        ),
        -- 获取当前出院记录之后的下一次入院日期
        NEXT_ADMIT = LEAD(CASE WHEN EVENT_TYPE = 'DISCHARGE' THEN EVENT_DATE END) OVER (
            PARTITION BY ID_NUM 
            ORDER BY EVENT_DATE ASC
        ),
        IS_DISCHARGE = CASE WHEN EVENT_TYPE = 'DISCHARGE' THEN 1 ELSE 0 END
    FROM AllEvents
)
SELECT 
    ID_NUM,
    ADMIT_DATE,
    EVENT_DATE AS DISCHARGE_DATE,
    -- 仅保留落在当前出院到下一次入院区间内的就诊记录
    CASE 
        WHEN IS_DISCHARGE = 1 AND NEXT_VISIT < COALESCE(NEXT_ADMIT, '9999-12-31')
        THEN NEXT_VISIT 
        ELSE NULL 
    END AS next_visit
FROM RankedEvents
WHERE IS_DISCHARGE = 1
ORDER BY DISCHARGE_DATE;

错误原因说明 #

之前的查询仅取了所有大于出院日期的就诊记录的最小值，没有考虑就诊记录是否落在当前出院到下一次入院的区间内。比如10月22日出院后的10月24日、11月7日就诊，实际是在11月22日入院之前的，不属于本次出院的后续就诊，因此该出院记录的next_visit应为NULL。

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