Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Find first appointment after discharge from...
Add to briefcase

Find first appointment after discharge from hospital Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
NineIron
Posted Monday, February 25, 2013 12:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 25, 2013 9:42 AM
Points: 55, Visits: 93
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')
Post #1423748
Erin Ramsay
Posted Monday, February 25, 2013 2:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:44 PM
Points: 419, Visits: 773
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
Post #1423772
Kingston Dhasian
Posted Tuesday, February 26, 2013 12:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 2,198, Visits: 4,148
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/
Post #1423907
NineIron
Posted Tuesday, February 26, 2013 6:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 25, 2013 9:42 AM
Points: 55, Visits: 93
Thanx for your help.
Post #1424041
NineIron
Posted Tuesday, February 26, 2013 6:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 25, 2013 9:42 AM
Points: 55, Visits: 93
Thanx for your help.
Post #1424042
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.