Detect consecutive dates in SQL and start counter

Question

Dates
2021-01-01
2021-01-02
2021-01-03
2021-01-10
2021-01-11
2021-01-12
2021-01-15

Expected output:

Dates	Counter
2021-01-01	1
2021-01-02	2
2021-01-03	3
2021-01-10	1
2021-01-11	2
2021-01-12	3
2021-01-15	1

Any idea how to achieve this ? I do know how to find min, max and count of the consecutive dates but need a counter that resets every time there is a gap in dates

Answer 1

One way to tackle such Gaps-And-Islands problem is to calculate a rank that's based on the difference with the previous date.

The method to calculate the difference in days between dates however depends on your RDBMS.

This example uses DATEDIFF (MS Sql Server)

SELECT 
 Dates, 
 ROW_NUMBER() OVER (PARTITION BY Rnk ORDER BY Dates) AS Counter
FROM
(
    SELECT 
     Dates, 
     SUM(CASE 
         WHEN DATEDIFF(day, prevDate, Dates) = 1 THEN 0 
         ELSE 1 END) OVER (ORDER BY Dates) AS Rnk 
    FROM
    (
      SELECT 
       Dates, 
       LAG(Dates) OVER (ORDER BY Dates) AS prevDate
      FROM your_table
    ) q1
) q2
ORDER BY Dates;