Detect consecutive dates in SQL and start counter

user_17144203 0
Viewed 236
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

gaps-and-islands sql
2 Answers

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;
user_40034190

You can use a self-join:

with cte as (
   select row_number() over (order by dt) r, dt from test
)
select c.dt, sum(c.dt = date_add(c1.dt, interval c.r-c1.r day)) 
from cte c join cte c1 on c1.r <= c.r group by c.dt order by c.dt
user_73267380
Related