How Can SQL Identify Consecutive Date Ranges?

Detect Consecutive Date Ranges Using SQL

Introduction

When preparing data for calendar applications, it is often necessary to extract consecutive date ranges from a sequence of dates. This task can be challenging, especially when the dates are not consistently spaced.

Solution Using SQL

To solve this problem using SQL, we can utilize the following approach:

1. Create a Row Number Column: Assign a row number to each date using the ROW_NUMBER() function. This creates a unique identifier for each date.
2. Group by Date Difference: Group the dates by the difference between their row numbers and the date itself. This difference is invariant within a consecutive sequence.
3. Find Start and End Dates: Determine the minimum and maximum dates within each group using MIN() and MAX() functions. These will represent the start and end dates of the consecutive range.

SQL Query

The following SQL query implements the above approach:

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

Result

The query will produce the following output:

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

This output provides the start and end dates of all consecutive date ranges in the original data.

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