SQL Server Next visit date after discharge

h7wcgrx3 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(94)

I asked a similar question yesterday and the code for it is below but it does not seem to be working for this situation. I am trying to find the next visit date after a discharge but if there are multiple discharges and the next visit date is after the second or third discharge I would like the "NEXT_VISIT" to be blank. Thank you.

HAVE:
| ID_NUM | ADMIT_DATE | DISCHARGE_DATE | NEXT_VISIT |
| ------------ | ------------ | ------------ | ------------ |
| 100000301 | 4/1/2022 | 4/7/2022 | 5/18/2022 |
| 100000301 | 4/11/2022 | 4/18/2022 | 5/18/2022 |
| 100000301 | 4/18/2022 | 5/13/2022 | 5/18/2022 |
| 100000005 | 8/25/2022 | 9/1/2022 | 9/12/2022 |
| 100000005 | 10/15/2022 | 10/22/2022 | 9/12/2022 |
| 100000005 | 10/22/2022 | 11/22/2022 | 9/12/2022 |

WANT:

ID_NUM	ADMIT_DATE	DISCHARGE_DATE	NEXT_VISIT
100000301	4/1/2022	4/7/2022
100000301	4/11/2022	4/18/2022
100000301	4/18/2022	5/13/2022	5/18/2022
100000005	8/25/2022	9/1/2022	9/12/2022
100000005	10/15/2022	10/22/2022	10/24/2022
100000005	10/22/2022	11/22/2022	11/28/2022

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

sql-server

来源：https://stackoverflow.com/questions/77114622/next-visit-date-after-discharge

2条答案

按热度按时间

watbbzwu1#

This works:

SELECT ID_NUM, Admit_Date, Discharge_Date, 
    CASE WHEN 
        COALESCE(LEAD(Discharge_Date) OVER (PARTITION BY id_num ORDER BY Discharge_Date), Discharge_Date) <= Discharge_Date 
        THEN Visit_Date END Visit_Date
FROM (
    select a.ID_NUM, a.Admit_Date, a.Discharge_Date, v.Service_Date Visit_Date
       , row_number() over (partition by a.id_num, a.admit_date order by v.Service_date, a.Discharge_date) rn
    from Admits a
    left join Visits v on v.id_num = a.id_num 
        and v.Service_Date > a.Discharge_Date
) t
WHERE rn = 1
ORDER BY Admit_Date

See it run here:

https://dbfiddle.uk/61vjkbAu

It uses windowing functions and should perform much better than using correlated subqueries in the SELECT list.

赞(0）回复(0）举报 2023-10-15

rxztt3cl2#

This will give you what you want, but please don't use it in production code.

SELECT a.*,
(CASE
    WHEN a.admit_date = (SELECT max(b.admit_date) FROM admits b WHERE a.id_num = b.id_num) 
        THEN (SELECT MIN(SERVICE_DATE) FROM VISITS V WHERE V.ID_NUM = A.ID_NUM AND v.SERVICE_DATE > a.DISCHARGE_DATE)
    END
) AS next_visit
FROM admits a;

SQL Fiddle

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Next visit date after discharge

2条答案

相关问题

热门标签

最新问答