Post reply

Find interventions within 48 hours of hospital discharge

  • March 17, 2016 at 5:31 am

    #399876

    I have two tables. One has discharges from the hospital. The other has interventions. The goal is to find the interventions that were followed up within 2 days of the discharge. For example a patient gets discharged on 1/1/2016 and the followup is on 1/2/2016. This would be a good thing. Problem for me is there can be several discharges for the same patient over time and there may be several followups, multiple phone calls, for the same discharge. So, you have to find the first occurance of an intervention that happens after the discharge. Also, there may be some discharges without any intervention. Any thoughts?

    create table #Encounters

    (

    PatientID varchar(10),

    DischargeDate datetime

    )

    insert into #Encounters(PatientID, DischargeDate) values('102144','11/13/2015')

    insert into #Encounters(PatientID, DischargeDate) values('102144','03/03/2016')

    insert into #Encounters(PatientID, DischargeDate) values('106812','10/01/2015')

    insert into #Encounters(PatientID, DischargeDate) values('106812','12/11/2015')

    insert into #Encounters(PatientID, DischargeDate) values('106812','12/15/2015')

    insert into #Encounters(PatientID, DischargeDate) values('106812','12/21/2015')

    insert into #Encounters(PatientID, DischargeDate) values('106812','02/05/2016')

    insert into #Encounters(PatientID, DischargeDate) values('107594','12/07/2015')

    insert into #Encounters(PatientID, DischargeDate) values('107594','12/25/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','10/21/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','10/24/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','11/10/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','11/22/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','11/23/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','11/29/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','12/02/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','12/06/2015')

    insert into #Encounters(PatientID, DischargeDate) values('108577','01/15/2016')

    insert into #Encounters(PatientID, DischargeDate) values('108577','02/12/2016')

    insert into #Encounters(PatientID, DischargeDate) values('115299','10/10/2015')

    insert into #Encounters(PatientID, DischargeDate) values('115568','12/28/2015')

    insert into #Encounters(PatientID, DischargeDate) values('116393','10/04/2015')

    insert into #Encounters(PatientID, DischargeDate) values('116393','10/20/2015')

    insert into #Encounters(PatientID, DischargeDate) values('116393','11/15/2015')

    insert into #Encounters(PatientID, DischargeDate) values('116393','11/20/2015')

    insert into #Encounters(PatientID, DischargeDate) values('116393','12/21/2015')

    insert into #Encounters(PatientID, DischargeDate) values('116393','12/28/2015')

    insert into #Encounters(PatientID, DischargeDate) values('116393','01/19/2016')

    insert into #Encounters(PatientID, DischargeDate) values('116393','02/09/2016')

    insert into #Encounters(PatientID, DischargeDate) values('116393','02/24/2016')

    insert into #Encounters(PatientID, DischargeDate) values('119217','11/14/2015')

    insert into #Encounters(PatientID, DischargeDate) values('119275','01/20/2016')

    insert into #Encounters(PatientID, DischargeDate) values('120251','10/28/2015')

    insert into #Encounters(PatientID, DischargeDate) values('120251','01/02/2016')

    insert into #Encounters(PatientID, DischargeDate) values('120251','01/22/2016')

    insert into #Encounters(PatientID, DischargeDate) values('120251','02/05/2016')

    insert into #Encounters(PatientID, DischargeDate) values('120251','02/16/2016')

    insert into #Encounters(PatientID, DischargeDate) values('121987','02/04/2016')

    insert into #Encounters(PatientID, DischargeDate) values('124139','01/24/2016')

    insert into #Encounters(PatientID, DischargeDate) values('125968','01/17/2016')

    insert into #Encounters(PatientID, DischargeDate) values('125968','02/24/2016')

    insert into #Encounters(PatientID, DischargeDate) values('12779','11/03/2015')

    insert into #Encounters(PatientID, DischargeDate) values('12779','11/13/2015')

    insert into #Encounters(PatientID, DischargeDate) values('12779','01/05/2016')

    insert into #Encounters(PatientID, DischargeDate) values('12779','01/13/2016')

    insert into #Encounters(PatientID, DischargeDate) values('12779','01/17/2016')

    insert into #Encounters(PatientID, DischargeDate) values('12779','02/02/2016')

    insert into #Encounters(PatientID, DischargeDate) values('12779','03/09/2016')

    insert into #Encounters(PatientID, DischargeDate) values('13202','10/07/2015')

    insert into #Encounters(PatientID, DischargeDate) values('13202','12/23/2015')

    insert into #Encounters(PatientID, DischargeDate) values('13202','01/21/2016')

    insert into #Encounters(PatientID, DischargeDate) values('13202','02/12/2016')

    insert into #Encounters(PatientID, DischargeDate) values('13202','03/03/2016')

    insert into #Encounters(PatientID, DischargeDate) values('135150','10/08/2015')

    insert into #Encounters(PatientID, DischargeDate) values('135150','10/26/2015')

    insert into #Encounters(PatientID, DischargeDate) values('135150','11/17/2015')

    insert into #Encounters(PatientID, DischargeDate) values('135150','01/05/2016')

    insert into #Encounters(PatientID, DischargeDate) values('135763','10/16/2015')

    insert into #Encounters(PatientID, DischargeDate) values('135763','11/18/2015')

    insert into #Encounters(PatientID, DischargeDate) values('135763','12/29/2015')

    insert into #Encounters(PatientID, DischargeDate) values('135763','01/08/2016')

    insert into #Encounters(PatientID, DischargeDate) values('135763','02/19/2016')

    insert into #Encounters(PatientID, DischargeDate) values('139108','10/26/2015')

    insert into #Encounters(PatientID, DischargeDate) values('139108','01/17/2016')

    insert into #Encounters(PatientID, DischargeDate) values('140200','11/05/2015')

    insert into #Encounters(PatientID, DischargeDate) values('140200','12/15/2015')

    insert into #Encounters(PatientID, DischargeDate) values('141385','02/01/2016')

    insert into #Encounters(PatientID, DischargeDate) values('141685','10/31/2015')

    insert into #Encounters(PatientID, DischargeDate) values('141685','11/06/2015')

    insert into #Encounters(PatientID, DischargeDate) values('141685','12/24/2015')

    insert into #Encounters(PatientID, DischargeDate) values('141685','01/11/2016')

    insert into #Encounters(PatientID, DischargeDate) values('146209','01/14/2016')

    insert into #Encounters(PatientID, DischargeDate) values('146619','02/15/2016')

    insert into #Encounters(PatientID, DischargeDate) values('14981','02/10/2016')

    insert into #Encounters(PatientID, DischargeDate) values('149925','12/22/2015')

    insert into #Encounters(PatientID, DischargeDate) values('151048','10/23/2015')

    insert into #Encounters(PatientID, DischargeDate) values('151048','11/05/2015')

    insert into #Encounters(PatientID, DischargeDate) values('151048','12/09/2015')

    insert into #Encounters(PatientID, DischargeDate) values('151048','01/25/2016')

    insert into #Encounters(PatientID, DischargeDate) values('151048','01/29/2016')

    insert into #Encounters(PatientID, DischargeDate) values('151048','02/26/2016')

    insert into #Encounters(PatientID, DischargeDate) values('161083','12/16/2015')

    insert into #Encounters(PatientID, DischargeDate) values('161083','12/26/2015')

    insert into #Encounters(PatientID, DischargeDate) values('161083','01/06/2016')

    insert into #Encounters(PatientID, DischargeDate) values('161083','01/23/2016')

    insert into #Encounters(PatientID, DischargeDate) values('161083','02/04/2016')

    insert into #Encounters(PatientID, DischargeDate) values('161083','03/01/2016')

    insert into #Encounters(PatientID, DischargeDate) values('161083','03/06/2016')

    insert into #Encounters(PatientID, DischargeDate) values('161171','11/24/2015')

    insert into #Encounters(PatientID, DischargeDate) values('161171','11/30/2015')

    insert into #Encounters(PatientID, DischargeDate) values('161171','12/06/2015')

    insert into #Encounters(PatientID, DischargeDate) values('161798','02/01/2016')

    insert into #Encounters(PatientID, DischargeDate) values('162103','11/28/2015')

    insert into #Encounters(PatientID, DischargeDate) values('162103','02/11/2016')

    insert into #Encounters(PatientID, DischargeDate) values('162103','03/09/2016')

    insert into #Encounters(PatientID, DischargeDate) values('163924','10/03/2015')

    insert into #Encounters(PatientID, DischargeDate) values('163970','10/05/2015')

    insert into #Encounters(PatientID, DischargeDate) values('163970','01/15/2016')

    insert into #Encounters(PatientID, DischargeDate) values('168779','01/07/2016')

    insert into #Encounters(PatientID, DischargeDate) values('171276','10/08/2015')

    create table #Intervention

    (

    PatientID varchar(10),

    InterventionDate datetime

    )

    insert into #Intervention(PatientID, InterventionDate) values('100312','02/22/2016')

    insert into #Intervention(PatientID, InterventionDate) values('100312','02/23/2016')

    insert into #Intervention(PatientID, InterventionDate) values('100312','02/24/2016')

    insert into #Intervention(PatientID, InterventionDate) values('102144','02/29/2016')

    insert into #Intervention(PatientID, InterventionDate) values('102778','11/30/2015')

    insert into #Intervention(PatientID, InterventionDate) values('106812','12/22/2015')

    insert into #Intervention(PatientID, InterventionDate) values('107594','12/22/2015')

    insert into #Intervention(PatientID, InterventionDate) values('107594','12/28/2015')

    insert into #Intervention(PatientID, InterventionDate) values('108577','01/15/2016')

    insert into #Intervention(PatientID, InterventionDate) values('109008','11/25/2015')

    insert into #Intervention(PatientID, InterventionDate) values('110383','01/25/2016')

    insert into #Intervention(PatientID, InterventionDate) values('110383','02/01/2016')

    insert into #Intervention(PatientID, InterventionDate) values('110383','02/02/2016')

    insert into #Intervention(PatientID, InterventionDate) values('110383','02/06/2016')

    insert into #Intervention(PatientID, InterventionDate) values('110383','02/08/2016')

    insert into #Intervention(PatientID, InterventionDate) values('111127','10/20/2015')

    insert into #Intervention(PatientID, InterventionDate) values('111583','02/12/2016')

    insert into #Intervention(PatientID, InterventionDate) values('112944','12/18/2015')

    insert into #Intervention(PatientID, InterventionDate) values('113299','02/18/2016')

    insert into #Intervention(PatientID, InterventionDate) values('113334','02/22/2016')

    insert into #Intervention(PatientID, InterventionDate) values('115299','10/07/2015')

    insert into #Intervention(PatientID, InterventionDate) values('115299','10/08/2015')

    insert into #Intervention(PatientID, InterventionDate) values('115299','10/15/2015')

    insert into #Intervention(PatientID, InterventionDate) values('115568','12/29/2015')

    insert into #Intervention(PatientID, InterventionDate) values('115568','01/06/2016')

    insert into #Intervention(PatientID, InterventionDate) values('116393','11/27/2015')

    insert into #Intervention(PatientID, InterventionDate) values('116393','12/10/2015')

    insert into #Intervention(PatientID, InterventionDate) values('116393','12/11/2015')

    insert into #Intervention(PatientID, InterventionDate) values('116393','12/29/2015')

    insert into #Intervention(PatientID, InterventionDate) values('116393','01/20/2016')

    insert into #Intervention(PatientID, InterventionDate) values('116393','02/12/2016')

    insert into #Intervention(PatientID, InterventionDate) values('116393','02/26/2016')

    insert into #Intervention(PatientID, InterventionDate) values('119217','12/02/2015')

    insert into #Intervention(PatientID, InterventionDate) values('119217','12/03/2015')

    insert into #Intervention(PatientID, InterventionDate) values('119217','12/04/2015')

    insert into #Intervention(PatientID, InterventionDate) values('119217','12/07/2015')

    insert into #Intervention(PatientID, InterventionDate) values('119217','02/12/2016')

    insert into #Intervention(PatientID, InterventionDate) values('119275','01/19/2016')

    insert into #Intervention(PatientID, InterventionDate) values('119275','01/20/2016')

    insert into #Intervention(PatientID, InterventionDate) values('119275','01/21/2016')

    insert into #Intervention(PatientID, InterventionDate) values('119664','11/30/2015')

    insert into #Intervention(PatientID, InterventionDate) values('119664','12/02/2015')

    insert into #Intervention(PatientID, InterventionDate) values('119664','12/04/2015')

    insert into #Intervention(PatientID, InterventionDate) values('120251','11/09/2015')

    insert into #Intervention(PatientID, InterventionDate) values('120251','12/04/2015')

    insert into #Intervention(PatientID, InterventionDate) values('120251','12/30/2015')

    insert into #Intervention(PatientID, InterventionDate) values('120251','01/21/2016')

    insert into #Intervention(PatientID, InterventionDate) values('120251','01/26/2016')

    insert into #Intervention(PatientID, InterventionDate) values('120251','02/01/2016')

    insert into #Intervention(PatientID, InterventionDate) values('120251','02/02/2016')

    insert into #Intervention(PatientID, InterventionDate) values('120251','02/12/2016')

    insert into #Intervention(PatientID, InterventionDate) values('121987','02/03/2016')

    insert into #Intervention(PatientID, InterventionDate) values('121987','02/04/2016')

    insert into #Intervention(PatientID, InterventionDate) values('121987','02/05/2016')

    insert into #Intervention(PatientID, InterventionDate) values('12291','11/25/2015')

    insert into #Intervention(PatientID, InterventionDate) values('12291','11/27/2015')

    insert into #Intervention(PatientID, InterventionDate) values('12291','12/14/2015')

    insert into #Intervention(PatientID, InterventionDate) values('12291','01/06/2016')

    insert into #Intervention(PatientID, InterventionDate) values('124139','01/18/2016')

    insert into #Intervention(PatientID, InterventionDate) values('124139','01/25/2016')

    insert into #Intervention(PatientID, InterventionDate) values('124139','01/26/2016')

    insert into #Intervention(PatientID, InterventionDate) values('124336','12/18/2015')

    insert into #Intervention(PatientID, InterventionDate) values('124336','12/22/2015')

    insert into #Intervention(PatientID, InterventionDate) values('124847','02/12/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','01/18/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','01/25/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','01/26/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','01/28/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/03/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/05/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/08/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/09/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/10/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/11/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/17/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/21/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/22/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/23/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/24/2016')

    insert into #Intervention(PatientID, InterventionDate) values('125968','02/26/2016')

    insert into #Intervention(PatientID, InterventionDate) values('1266','11/04/2015')

    insert into #Intervention(PatientID, InterventionDate) values('1266','12/16/2015')

    insert into #Intervention(PatientID, InterventionDate) values('12779','12/28/2015')

    insert into #Intervention(PatientID, InterventionDate) values('12779','12/30/2015')

    insert into #Intervention(PatientID, InterventionDate) values('12779','01/13/2016')

    insert into #Intervention(PatientID, InterventionDate) values('130274','01/14/2016')

    insert into #Intervention(PatientID, InterventionDate) values('130274','01/15/2016')

    insert into #Intervention(PatientID, InterventionDate) values('131223','10/21/2015')

    insert into #Intervention(PatientID, InterventionDate) values('131949','10/07/2015')

    insert into #Intervention(PatientID, InterventionDate) values('13202','12/21/2015')

    insert into #Intervention(PatientID, InterventionDate) values('13202','12/24/2015')

    insert into #Intervention(PatientID, InterventionDate) values('13202','12/28/2015')

    insert into #Intervention(PatientID, InterventionDate) values('13202','12/29/2015')

    insert into #Intervention(PatientID, InterventionDate) values('13202','01/06/2016')

    insert into #Intervention(PatientID, InterventionDate) values('13202','01/08/2016')

    insert into #Intervention(PatientID, InterventionDate) values('13202','01/15/2016')

    insert into #Intervention(PatientID, InterventionDate) values('13202','02/02/2016')

    insert into #Intervention(PatientID, InterventionDate) values('13202','02/09/2016')

    insert into #Intervention(PatientID, InterventionDate) values('13202','02/10/2016')

    insert into #Intervention(PatientID, InterventionDate) values('13202','02/12/2016')

  • March 17, 2016 at 5:45 am

    #1865825

    This is one way to do it:

    SELECT *

    FROM #Encounters AS ENC

    CROSS APPLY (

    SELECT TOP 1 *

    FROM #Intervention AS ITV

    WHERE PatientID = ENC.PatientID

    AND InterventionDate <= DATEADD(day, 2, ENC.DischargeDate)

    AND InterventionDate > ENC.DischargeDate

    ORDER BY InterventionDate

    ) AS whoops

    You didn't post the expected output, so I can't say if it's correct or not.

    -- Gianluca Sartori

  • March 17, 2016 at 6:07 am

    #1865829

    Can you tweak the query to show each discharge and how many days between the discharge date and the first intervention date that happens after the discharge date? Could you include the patient discharge record where there is no intervention? For example..........

    PatientID DischargeDate InterventionDate Days

    12345 1/1/2016 NULL NULL

    54321 1/1/2016 1/2/2016 1

    54321 1/10/2016 1/15/2016 5

    98765 1/1/2016 1/3/2016 2

    where patients may have multiple interventions but, only return the first one.

    I appologize if this doesn't make sense.

  • March 17, 2016 at 6:19 am

    #1865830

    Better?

    SELECT ENC.PatientID,

    ENC.DischargeDate,

    ITV.InterventionDate,

    Days = DATEDIFF(day, DischargeDate, InterventionDate)

    FROM #Encounters AS ENC

    OUTER APPLY (

    SELECT TOP 1 *

    FROM #Intervention AS ITV

    WHERE PatientID = ENC.PatientID

    AND InterventionDate <= DATEADD(day, 2, ENC.DischargeDate)

    AND InterventionDate > ENC.DischargeDate

    ORDER BY InterventionDate

    ) AS ITV

    -- Gianluca Sartori

  • March 17, 2016 at 6:28 am

    #1865833

    Perfect. Thanx.

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

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