Next visit date after discharge
-
I asked same question on stack but only solutions have not returned correct output. 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.
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 WORKS FOR ONE BUT NOT THE OTHER
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
---THIS WORKS FOR ONE BUT NOT THE OTHER
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 -
;WITH CTE_ADMITS AS (
SELECT A.*, ISNULL(LEAD(A.DISCHARGE_DATE, 1) OVER(PARTITION BY ID_NUM ORDER BY DISCHARGE_DATE), '20790601') AS NEXT_DISCHARGE
FROM ADMITS A
)
SELECT A.*, V.SERVICE_DATE
FROM CTE_ADMITS A
OUTER APPLY (
SELECT TOP (1) V.*
FROM VISITS V
WHERE V.ID_NUM = A.ID_NUM
AND V.SERVICE_DATE > A.DISCHARGE_DATE
AND V.SERVICE_DATE < A.NEXT_DISCHARGE
ORDER BY V.SERVICE_DATE
) AS VSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Thank you so much. I've never seen/used OUTER APPLY before.
-
You're welcome. Yeah, OUTER APPLY is pretty useful, like here when you kinda want a join but only want 1 row, not all of them.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Would I need to do a LEAD to compare the next_visit to the admit and discharge dates? The 10/24/22 one is during the next admit and now they would like them removed if is during the admission.
-
I'll take a look at it a bit later today.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply