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.

    delete

     

    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 V

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply