Find first appointment after discharge from hospital

  • I need to find the first doctor's appointment after a patient is discharged from the hospital then, include the number of days between DischargeDate and Appointment Date. I've included some code to create two tables, Discharges and Appointments.

    create table Discharges

    (

    UnitNumber varchar(6),

    IDXMRN varchar(6),

    AdmitDate datetime,

    DischargeDate datetime,

    InsuranceID varchar(10),

    Sequence int

    (

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('396232','328550',2012-01-08,2012-01-09,'MCR',1)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('440289','747571',2011-09-17,2011-09-17,'THPMCRBG',1)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('440289','747571',2011-11-06,2011-11-08,'THPMCRBG',2)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('504869','777703',2011-07-15,2011-07-17,'UHC',1)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('504869','777703',2011-08-05,2011-08-12,'EVERCARE',2)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('504869','777703',2012-01-12,2012-01-13,'EVERCARE',3)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('505596','309918',2011-12-07,2011-12-10,'MCR',1)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('540072','337766',2011-08-27,2011-09-03,'MCR',1)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('570697','710792',2011-07-12,2011-07-15,'THPMCRBG',1)

    insert into Discharges(UnitNumber, IDXMRN, AdmitDate, DischargeDate, InsuranceID, Sequence) values('570697','710792',2011-08-11,2011-08-12,'THPMCRBG',2)

    create table Appointments

    (

    UnitNumber varchar(6),

    IDXMRN varchar(6),

    ApptDt2 datetime

    (

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2011-08-25')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2011-09-27')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2011-11-03')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-01-03')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-01-12')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-01-31')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-02-07')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-02-28')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-04-03')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-05-04')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-05-11')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('396232','328550','2012-05-21')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2011-09-19')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2011-10-04')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2011-10-07')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2011-10-26')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2011-10-31')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2011-11-11')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2011-12-06')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2011-12-16')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2012-02-15')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2012-04-09')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2012-06-15')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2012-09-24')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2012-10-15')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2012-12-04')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2012-12-12')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2013-01-11')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2013-01-28')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2013-02-04')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('440289','747571','2013-02-11')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2011-08-01')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2011-08-26')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2011-09-09')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2011-10-31')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2011-11-10')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2011-12-09')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2011-12-23')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-01-20')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-02-06')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-03-08')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-03-22')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-05-03')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-05-03')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-05-10')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-05-14')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-06-07')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-06-14')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-07-30')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-08-03')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-10-02')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-10-15')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-11-08')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-11-27')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2012-12-21')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2013-02-01')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('504869','777703','2013-02-15')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('505596','309918','2011-11-29')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('505596','309918','2012-02-15')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('505596','309918','2012-06-29')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('505596','309918','2012-09-26')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2011-08-11')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2011-08-25')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2011-08-26')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2011-09-23')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2011-11-15')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2011-12-15')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2011-12-20')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2012-01-06')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2012-01-12')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2012-03-23')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2012-04-27')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2012-07-20')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2012-08-16')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2012-09-13')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2013-01-17')

    insert into Appointments(UnitNumber, IDXMRN, ApptDt2) values('540072','337766','2013-02-21')

  • I'm sure you can clean this up but it will do what you want.

    ; with cte

    as

    (select d.idxmrn, d.dischargedate, a.apptdt2, DATEDIFF(d,d.dischargedate, a.apptdt2) daysSince, ROW_NUMBER() over (PARTITION by d.idxmrn order by d.idxmrn,DATEDIFF(d,d.dischargedate, a.apptdt2)) as rownum

    from discharges d

    inner join appointments a

    on d.idxmrn = a.idxmrn

    group by d.idxmrn, d.dischargedate, a.apptdt2

    having MAX(d.dischargedate)<MIN(a.apptdt2)

    )

    select * from cte where rownum = 1

  • Another way using CROSS-APPLY

    SELECTD.UnitNumber, D.IDXMRN, D.DischargeDate, App.ApptDt2, DATEDIFF( DAY, D.DischargeDate, App.ApptDt2 ) AS Days_Difference

    FROMDischarges AS D

    OUTER APPLY(

    SELECTTOP 1 *

    FROMAppointments AS A

    WHERED.UnitNumber = A.UnitNumber AND D.IDXMRN = A.IDXMRN

    ANDD.DischargeDate < A.ApptDt2

    ) AS App


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thanx for your help.

  • Thanx for your help.

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

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