CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Iain Ward
Iain Ward

Reputation: 9936

T-SQL - SELECT by nearest date and GROUPED BY ID

From the data below I need to select the record nearest to a specified date for each Linked ID using SQL Server 2005:

ID     Date      Linked ID
...........................
1    2010-09-02     25
2    2010-09-01     25
3    2010-09-08     39
4    2010-09-09     39
5    2010-09-10     39
6    2010-09-10     34
7    2010-09-29     34
8    2010-10-01     37
9    2010-10-02     36
10   2010-10-03     36

So selecting them using 01/10/2010 should return:

1    2010-09-02     25 
5    2010-09-10     39
7    2010-09-29     34 
8    2010-10-01     37
9    2010-10-02     36

I know this must be possible, but can't seem to get my head round it (must be too near the end of the day :P) If anyone can help or give me a gentle shove in the right direction it would be greatly appreciated!

EDIT: Also I have come across this sql to get the closest date:

abs(DATEDIFF(minute, Date_Column, '2010/10/01'))

but couldn't figure out how to incorporate into the query properly...

Thanks

Upvotes: 5

Views: 13600

Answers (3)

pcofre
pcofre

Reputation: 4066

You can also try to do it with a subquery in the select statement:

select  [LinkedId],
        (select top 1 [Date] from [Table] where [LinkedId]=x.[LinkedId] order by abs(DATEDIFF(DAY,[Date],@date)))
from    [Table] X
group by [LinkedId]

Upvotes: 0

Hogan
Hogan

Reputation: 70523

You want to look at the absolute value of the DATEDIFF function (http://msdn.microsoft.com/en-us/library/ms189794.aspx) by days.

The query can look something like this (not tested)

with absDates as 
(
   select *, abs(DATEDIFF(day, Date_Column, '2010/10/01')) as days
   from table
), mdays as
( 
   select min(days) as mdays, linkedid
   from absDates
   group by linkedid
)
select * 
from absdates
inner join mdays on absdays.linkedid = mdays.linkedid and absdays.days = mdays.mdays

Upvotes: 4

bobs
bobs

Reputation: 22184

you can try this.

DECLARE @Date DATE = '10/01/2010';

WITH cte AS
    (
    SELECT ID, LinkedID, ABS(DATEDIFF(DD, @date, DATE)) diff,
        ROW_NUMBER() OVER (PARTITION BY LinkedID ORDER BY ABS(DATEDIFF(DD, @date, DATE))) AS SEQUENCE
    FROM MyTable
    )

SELECT *
FROM cte
WHERE SEQUENCE = 1
ORDER BY ID
;

You didn't indicate how you want to handle the case where multiple rows in a LinkedID group represent the closest to the target date. This solution will only include one row And, in this case you can't guarantee which row of the multiple valid values is included.

You can change ROW_NUMBER() with RANK() in the query if you want to include all rows that represent the closest value.

Upvotes: 8

Related Questions