Search code examples
sql-serverdatabaset-sqldatetimedatediff

T-SQL date difference between multiple rows

I want the Id_Pass where the difference between 2 or more dates is more than 10 days. I've tried datediff but I feel it's getting too complex.

+------------+-----------+-------+
|    Date    |  Id_Pass  | Value |
+------------+-----------+-------+
| 2011-03-18 | PASS00004 |    30 |
| 2011-03-19 | PASS00004 |    60 |
| 2012-02-25 | PASS00005 |    30 |
| 2012-04-25 | PASS00005 |    30 |
+------------+-----------+-------+

Desired result:

+-----------+
|  Id_Pass  |
+-----------+
| PASS00005 |
+-----------+
Solution

  • Just use the LAG window function to find previous date for each row:

    SELECT Id_Pass
FROM (
    SELECT Id_Pass, Date, LAG(Date) OVER (PARTITION BY Id_Pass ORDER BY Date) AS PrevDate
    FROM yourdata
) AS cte
WHERE Date > DATEADD(DAY, 10, PrevDate)