How to Identify Consecutive Date Ranges in SQL?

Detect Consecutive Dates Ranges Using SQL

To identify consecutive date ranges in a column containing a sequence of dates, SQL can be employed to efficiently extract the start and end dates of each distinct range.

Consider the following dataset:

InfoDate

2013-12-04
2013-12-05
2013-12-06

2013-12-09
2013-12-10

2014-01-01
2014-01-02
2014-01-03

2014-01-06
2014-01-07

2014-01-29
2014-01-30
2014-01-31

2014-02-03
2014-02-04

The goal is to extract the start and end dates of each consecutive date range, resulting in:

StartDate     EndDate

2013-12-04    2013-12-06
2013-12-09    2013-12-10
2014-01-01    2014-01-03
2014-01-06    2014-01-07
2014-01-29    2014-01-31
2014-02-03    2014-02-04

A simplified and efficient SQL solution can be devised using the following steps:

1. Assign a unique row number to each date in the InfoDate column, creating a helper table named t. This numbering facilitates the grouping of consecutive dates.
2. Group the rows in t by the difference between the date and its corresponding row number. This grouping creates "gaps" in the sequence of dates.
3. Within each group, the minimum and maximum dates represent the start and end dates of the consecutive date range.

The SQL query to achieve this is:

WITH t AS (
  SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i
  FROM @d
  GROUP BY InfoDate
)
SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(day,i,d)

The above is the detailed content of How to Identify Consecutive Date Ranges in SQL?. For more information, please follow other related articles on the PHP Chinese website!