Find first appointment after discharge from hospital

• NineIron

  SSChampion

  Points: 12536

  More actions

  February 25, 2013 at 12:36 pm

  #274557

  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')

• Erin Ramsay

  SSCrazy Eights

  Points: 9728

  More actions

  February 25, 2013 at 2:11 pm

  #1590940

  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

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  February 26, 2013 at 12:38 am

  #1591042

  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/

• NineIron

  SSChampion

  Points: 12536

  More actions

  February 26, 2013 at 6:09 am

  #1591155

  Thanx for your help.

• NineIron

  SSChampion

  Points: 12536

  More actions

  February 26, 2013 at 6:10 am

  #1591156

  Thanx for your help.