Get rows where the difference between two date columns is minimal
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 a row for each user where the difference between
dateAssigned
and is dateComment
minimal? In my example, the query should return rows 2 and 3.
Thank.
source to share
You can use expression CTE
(Common Table Expression) and : ROW_NUMBER
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
source to share
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
The number of rows is 1 for those records that match the minimum difference. Therefore, the where clause in the outer select expression retrieves the required records.
EDIT:
I added an ABS function to apply to date differences since dateAssigned can also come before dateComment.
source to share