CODEWITHSUNDEEP
sqlsql-server
Saleem
Saleem

Reputation: 730

Get Rows Where Difference Between Two Date Columns is Minimum

I have the following table (this is just a sample):

id  User    dateAssigned          dateComment
---|-------|---------------------|---------------------|
1  | Usr1  | 2014-12-02 12:35:00 | 2014-12-03 08:13:00 |
2  | Usr1  | 2014-12-02 12:35:00 | 2014-12-02 13:06:00 |
3  | Usr2  | 2014-12-02 07:47:00 | 2014-12-02 07:47:00 |
4  | Usr2  | 2014-12-02 07:47:00 | 2014-11-25 08:07:00 |

How do I write a query in SQL Server 2008 to select for each user the row where difference between dateAssigned and dateComment is minimum? In my example, query should return rows 2 and 3.

Thank you.

Upvotes: 4

Views: 3633

Answers (3)

Rudolf Yurgenson
Rudolf Yurgenson

Reputation: 603

Just for fun, of course row_number + partition by is better and faster

select * from #t
outer apply (
  select top 1 id from #t t1
  where t1.usr = #t.usr
  order by abs(datediff(second, dateAssigned, dateComment))
) o
where #t.id = o.id

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Use this:

SELECT * 
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Username 
                               ORDER BY ABS(DATEDIFF(second, dateComment, dateAssigned)) ASC) AS datesOrder
  FROM @T ) t
WHERE t.datesOrder = 1

Row number is equal to 1 for those records corresponding to the minimum difference. Hence, the where clause in the outer select statement retrieves the records desired.

EDIT:

I added the ABS function applied to the date difference, since dateAssigned can also precede dateComment.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460048

You could use a CTE(Common Table Expression) and ROW_NUMBER function:

WITH CTE AS
(
    SELECT id,  [User], dateAssigned, dateComment,
           rn = ROW_NUMBER() OVER ( 
                     PARTITION BY [User] 
                     ORDER BY ABS(DATEDIFF(minute, dateAssigned, dateComment)) ASC)
    FROM dbo.Users u
)
SELECT id, [User], dateAssigned, dateComment
FROM CTE
WHERE RN = 1

Upvotes: 7

Related Questions