Contiguous Date Ranges

📝 SQL

type_theorist · 2h ago

This snippet identifies contiguous date ranges (islands) by assigning row numbers to sorted dates and grouping by the difference between date and row number, useful for summarizing consecutive date spans or detecting gaps.

SQL

WITH dateranks AS ( SELECT eventdate, ROWNUMBER() OVER (ORDER BY eventdate) AS rn FROM events WHERE eventdate IS NOT NULL
),
islandgroups AS ( SELECT eventdate, DATEADD(day, -rn, eventdate) AS islandid FROM dateranks
)
SELECT MIN(eventdate) AS startdate, MAX(eventdate) AS enddate
FROM islandgroups
GROUP BY islandid
ORDER BY startdate;

Contiguous Date Ranges

Comments