How to Efficiently Detect and Extract Consecutive Date Ranges in SQL?

Detecting Consecutive Date Ranges in SQL

Introduction

In various applications, we frequently encounter the need to group and extract consecutive date ranges from a given input. This task arises in situations such as calendar population or data analysis. By leveraging SQL's powerful capabilities, we can efficiently solve the problem without the need for complex joins or recursive CTEs.

Problem Definition

Consider a column named 'InfoDate' that contains a sequence of dates, as illustrated below:

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 objective is to identify each consecutive date range and extract the start and end dates for each interval. As illustrated in the example below, consecutive dates should be grouped together to form a range:

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

SQL Solution

To achieve this result using SQL, we utilize a technique that employs the combination of row numbering and date differencing. By using the ROW_NUMBER() function, we assign a sequential number ('i') to each row of the 'InfoDate' column and create a column named 'd'. This column represents the difference between each date and its corresponding row number.

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

The following step involves grouping the rows by the difference between the 'i' and 'd' columns (DATEDIFF(day,i,d)). This grouping allows us to identify consecutive dates since they will share the same difference value.

SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(day,i,d)

By using the MIN() and MAX() functions within each group, we can determine the start and end dates of each consecutive range.

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

In this way, we have efficiently extracted the consecutive date ranges using a purely SQL-based approach.

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