Get Rows Where Difference Between Two Date Columns is Minimum

Question

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.

Answer 1

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

Answer 2

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.