I have a series of dates in a result thus (the order is important):

2020-09-24
2020-09-22
2020-09-23
2020-09-21
2020-09-17
2020-09-18
2020-09-16
2020-09-28
2020-09-25
2009-05-13
2008-10-24
2009-05-23

And I need to remove dates where there are not more than 14 days since the last used date, starting with 2020-09-24 so:

2020-09-24
2009-05-13
2008-10-24

Would be the result.

I've been wracking my head trying to work out how to do this but can not work out how to roll the last 'good' value to compare against... Is there a way in sql to do this?

CodePudding user response：

You can use a LAG and DATEDIFF to get the number of days that have passed since the last row. Then, if you put that with a CTE (or derived table) you can then filter on that expression:

WITH CTE AS(
    SELECT V.YourColumn,
           DATEDIFF(DAY,LAG(V.YourColumn) OVER (ORDER BY V.YourColumn),V.YourColumn) AS DaysPassed
    FROM (VALUES(CONVERT(date,'20200924')),
                (CONVERT(date,'20200922')),
                (CONVERT(date,'20200923')),
                (CONVERT(date,'20200921')),
                (CONVERT(date,'20200917')),
                (CONVERT(date,'20200918')),
                (CONVERT(date,'20200916')),
                (CONVERT(date,'20200928')),
                (CONVERT(date,'20200925')),
                (CONVERT(date,'20090513')),
                (CONVERT(date,'20081024')),
                (CONVERT(date,'20090523')))V(YourColumn))
SELECT YourColumn
FROM CTE
WHERE DaysPassed IS NULL OR DaysPassed > 14; --"First" row will be NULL