SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find first appointment after discharge from hospital


Find first appointment after discharge from hospital

Author
Message
NineIron
NineIron
SSC Eights!
SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)

Group: General Forum Members
Points: 835 Visits: 656
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
Erin Ramsay
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 1099
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
Kingston Dhasian
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3707 Visits: 5180
Another way using CROSS-APPLY

SELECT   D.UnitNumber, D.IDXMRN, D.DischargeDate, App.ApptDt2, DATEDIFF( DAY, D.DischargeDate, App.ApptDt2 ) AS Days_Difference
FROM Discharges AS D
OUTER APPLY (
SELECT TOP 1 *
FROM Appointments AS A
WHERE D.UnitNumber = A.UnitNumber AND D.IDXMRN = A.IDXMRN
AND D.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
NineIron
SSC Eights!
SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)

Group: General Forum Members
Points: 835 Visits: 656
Thanx for your help.
NineIron
NineIron
SSC Eights!
SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)

Group: General Forum Members
Points: 835 Visits: 656
Thanx for your help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search