Returning the closest matching date (absolute valu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning the closest matching date (absolute valu

Author

Topic

tmb7w
Starting Member

2 Posts

Posted - 2011-10-26 : 21:02:24

Hi, I am using SSRS 2005 and am trying to create a query that returns the closest matching date between two dates held in different tables.

Say you have one table that holds patient, bed, and discharge date information.

PatientNumber BedID Discharge Date
1 15 10/11/2011 14:35
2 18 10/12/2011 16:33

And a second table that holds when beds become dirty (i.e. the bed needs to be cleaned after a patient has left the hospital).

BedID DirtyDate
15 10/1/2011 5:45
15 10/11/2011 14:37
15 10/27/2011 4:33
18 9/3/2011 8:33
18 10/12/2011 16:30
18 10/28/2011 8:35

I want to find out when the bed became dirty which can be found by figuring out what the closest dirty date is to the discharge date. Note the bed may become dirty before or after the discharge date. The desired result for this particular example is the following...

PatientNumber BedID DC Date DirtyDate
1 15 10/11/2011 14:35 10/11/2011 14:37
2 18 10/12/2011 16:33 10/12/2011 16:30

I have been able to find the closest matching difference by using a Min(ABS(DateDiff(n, DCDate, DirtyDate))) query, but I haven't been able to figure out how to also return the DirtyDate value in the query because it causes every date difference to be returned.

There has got to be a way; probably an easier one, but I am confused at this point.

--------------

So I am using either this query which returns just the smallest date difference and not the dirtydate...

DECLARE

@PATIENT TABLE

(

PatientNumber

int,
BedID

int,
DischargeDate

DateTime)
DECLARE

@BED TABLE

(

BedID

int,
DirtyDate

DateTime)
Insert

into @Patient (PatientNumber, BedID, DischargeDate

)

Values

(

1,15,'10/11/2011 14:35'

)

Insert

into @Patient (PatientNumber, BedID, DischargeDate

)

Values

(

2,18,'10/12/2011 16:33'

)

Insert

into @Bed (BedID, DirtyDate

)

Values

(

15, '10/1/2011 5:45'

)

Insert

into @Bed (BedID, DirtyDate

)

Values

(

15, '10/11/2011 14:37'

)

Insert

into @Bed (BedID, DirtyDate

)

Values

(

15, '10/27/2011 4:33'

)

Insert

into @Bed (BedID, DirtyDate

)

Values

(

18, '9/3/2011 8:33'

)

Insert

into @Bed (BedID, DirtyDate

)

Values

(

18, '10/12/2011 16:30'

)

Insert

into @Bed (BedID, DirtyDate

)

Values

(

18, '10/28/2011 8:35'

)

SELECT

pat.PatientNumber, bed.BedID, pat.DischargeDate, Min(ABS(DATEDIFF(n,
bed

.DirtyDate, pat.DischargeDate))) As SmallestDateDifferenceMinutes

FROM

@PATIENT as pat INNER
JOIN

@Bed

as bed ON pat.BedID =bed.BedID
Group

By pat.PatientNumber, bed.BedID, pat.DischargeDate

Resulting data, correct except I also want the dirty date...

PatientNumber BedID DischargeDate SmallestDateDifferenceMinutes
1 15 10/11/11 14:35 2
2 18 10/12/11 16:33 3

Or this query that returns the whole shebang....

SELECT

pat.PatientNumber, bed.BedID, pat.DischargeDate, bed.DirtyDate, Min(ABS(DATEDIFF(n,

bed

.DirtyDate, pat.DischargeDate))) As
SmallestDateDifferenceMinutes

FROM

@PATIENT as pat INNER

JOIN

@Bed

as bed ON pat.BedID =bed.
BedID

Group

By pat.PatientNumber, bed.BedID, pat.DischargeDate, bed.DirtyDate

PatientNumber BedID DischargeDate DirtyDate SmallestDateDifferenceMinutes
1 15 10/11/11 14:35 10/1/11 5:45 14930
1 15 10/11/11 14:35 10/11/11 14:37 2
1 15 10/11/11 14:35 10/27/11 4:33 22438
2 18 10/12/11 16:33 9/3/11 8:33 56640
2 18 10/12/11 16:33 10/12/11 16:30 3
2 18 10/12/11 16:33 10/28/11 8:35 22562

How do I get the desired result? Thanks again. Tim

Desired table...

PatientNumber BedID DischargeDate DirtyDate
1 15 10/11/11 14:35 10/11/11 14:37
2 18 10/12/11 16:33 10/12/11 16:30

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-10-26 : 21:57:27

Try this:


SELECT pat.PatientNumber, pat.BedID, pat.DischargeDate,
       bed.DirtyDate
  FROM @PATIENT as pat
       CROSS APPLY
       (SELECT TOP(1)
               bed.DirtyDate
          FROM @Bed as bed
         WHERE bed.BedID = pat.BedID
         ORDER BY ABS(DATEDIFF(n, bed.DirtyDate, pat.DischargeDate))) AS bed;

tmb7w
Starting Member

2 Posts

Posted - 2011-10-28 : 13:12:04

BOOOOOOM! You got it! Thanks, malpashaa. Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources