Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find first appointment after discharge from hospital Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 12:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:05 AM
Points: 135, Visits: 206
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
Posted Monday, February 25, 2013 2:11 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:57 PM
Points: 546, Visits: 1,062
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
Posted Tuesday, February 26, 2013 12:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 2,682, Visits: 4,745
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
Posted Tuesday, February 26, 2013 6:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:05 AM
Points: 135, Visits: 206
Thanx for your help.
Post #1424041
Posted Tuesday, February 26, 2013 6:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:05 AM
Points: 135, Visits: 206
Thanx for your help.
Post #1424042
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse