Selecting the closest date... - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Selecting the closest date...

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2011-05-18 : 14:19:32

I currently have a query that works, but I'm curious to know if there is a better way to do this.

What I have basically, is a table with values with an effective date for each value. The query needs to choose the value with the most recent effective date, which does not come after the date on the associated record.

Here is an example:


Lookup values:
Incident - Value - Date
A..........5.......1/1/2004
A..........8.......1/1/2005
A..........4.......1/1/2006
B..........1.......2/1/2004
B..........3.......2/1/2005
B..........2.......2/1/2006
C..........20......6/1/2004
C..........50......6/1/2005
C..........36......6/1/2006

Now the data that this would be appended to would be along the following lines:


Incidents:
Incident - Date
A..........3/24/2005
B..........6/08/2006
C..........9/09/2008
A..........1/01/2003

So, the joined data should look like this:


Incident - Date --- Incident - Value - Date
A........3/24/2005..A..........8.......1/1/2005
B........6/08/2006..B..........2.......2/1/2006
C........9/09/2008..C..........36......6/1/2006
A........1/01/2003..NULL

What I'm currently doing is using a subquery in the join, like this:


SELECT *
FROM 
  Incidents i
    LEFT JOIN Elements e ON e.incident = i.incident AND
                            e.date = (SELECT MAX(date) FROM Elements WHERE date <= i.date AND incident = i.incident)

As you can imagine, this takes a while to run on large data sets.

Is there a better wya to do this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 14:32:04

Another way to do it - don't know if it will be better - intuitively it seems like it should - is as follows:


SELECT
   i.*,e.*
FROM
   Incidents i
   OUTER APPLY
   ( 
      SELECT TOP 1 *
      FROM ELEMENTS e 
      WHERE e.date <= i.date AND e.incident = i.incident 
      ORDER BY e.date DESC
   )e

malachi151
Posting Yak Master

152 Posts

Posted - 2011-05-18 : 15:45:42

This looks to be a winner. On the set of data that I was running those query on, using the JOIN took about 4.5 minutes, the APPLY took about 1.3 minutes.

quote:
Originally posted by sunitabeck

Another way to do it - don't know if it will be better - intuitively it seems like it should - is as follows:

SELECT
i.*,e.*
FROM
Incidents i
OUTER APPLY
(
SELECT TOP 1 *
FROM ELEMENTS e
WHERE e.date <= i.date AND e.incident = i.incident
ORDER BY e.date DESC
)e

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-05-18 : 19:21:00

I have no idea if this would be any faster but...[CODE]select
a.Incident,
a.date,
a.Value,
a.EffDate
from (
select
i.Incident,
i.date,
e.Value,
e.date EffDate,
row_number() over(partition by e.incident ORDER BY e.date DESC) rn
from
Incident i
LEFT OUTER JOIN
Elements e
ON e.incident = i.incident
AND e.date <= i.date
) a
where rn = 1[/CODE]

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources