question

ncthomp121063 avatar image
ncthomp121063 asked

Return record with date with specific time period

I need to return a record for patients with a admit date within 48 hours of a discharge date, the fields that I have to work with are Urn(unique record number),PatientID, AdmitDate, DischargeDate. Urn PatientID AdmitDate DischargeDate H000284152 274952 2009-11-13 13:21:00.000 2009-11-13 14:45:00.000 H000284152 274952 2010-06-12 13:57:00.000 2010-06-12 15:25:00.000 H000284152 274952 2010-06-17 10:02:00.000 2010-06-17 10:50:00.000 H000284152 274952 2012-09-30 20:13:00.000 2012-09-30 22:23:00.000 H000284152 274952 2013-02-28 10:02:00.000 2013-02-28 11:06:00.000 H000284152 274952 2013-03-03 13:33:00.000 2013-03-03 15:47:00.000 H000284152 274952 2013-03-03 20:09:00.000 2013-03-03 20:40:00.000 H000284152 274952 2013-03-04 14:20:00.000 2013-03-04 16:12:00.000 so I trying to compare the admitdate is within 48 of the previous dischargedate, and it has me stumped. Any help would be appreciated.
date-functions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
This looks very similar to a [question I answered recently][1], inasmuch as the approach to solving it is the same. --edit-- What, you want the answer? DECLARE @PatientData TABLE ( URN VARCHAR(20) , PatientID BIGINT , AdmitDate DATETIME , DischargeDate DATETIME ) INSERT INTO @PatientData VALUES ( 'H000284152', 274952, '2009-11-13 13:21:00.000', '2009-11-13 14:45:00.000' ), ( 'H000284152', 274952, '2010-06-12 13:57:00.000', '2010-06-12 15:25:00.000' ), ( 'H000284152', 274952, '2010-06-17 10:02:00.000', '2010-06-17 10:50:00.000' ), ( 'H000284152', 274952, '2012-09-30 20:13:00.000', '2012-09-30 22:23:00.000' ), ( 'H000284152', 274952, '2013-02-28 10:02:00.000', '2013-02-28 11:06:00.000' ), ( 'H000284152', 274952, '2013-03-03 13:33:00.000', '2013-03-03 15:47:00.000' ), ( 'H000284152', 274952, '2013-03-03 20:09:00.000', '2013-03-03 20:40:00.000' ), ( 'H000284152', 274952, '2013-03-04 14:20:00.000', '2013-03-04 16:12:00.000' ) SELECT * FROM @PatientData; WITH OrderedPatientData AS ( SELECT URN , PatientID , ROW_NUMBER() OVER ( PARTITION BY PatientID ORDER BY AdmitDate ) AS RowNum , AdmitDate , DischargeDate FROM @PatientData ), JoinedOrderedPatientData AS ( SELECT opd1.URN , opd1.PatientID , opd1.AdmitDate AS AdmitDate , opd1.DischargeDate AS DischargeDate , opd2.AdmitDate AS NextAdmitDate , opd2.DischargeDate AS NextDischargeDate FROM OrderedPatientData opd1 LEFT JOIN OrderedPatientData opd2 ON opd1.URN = opd2.URN AND opd1.RowNum = opd2.RowNum - 1 ) SELECT * , DATEDIFF(hour, DischargeDate, NextAdmitDate) FROM JoinedOrderedPatientData WHERE DATEDIFF(hour, DischargeDate, NextAdmitDate) <= 48 [1]: http://ask.sqlservercentral.com/questions/99219/get-difference-between-multiple-consicutive-dates.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.