How to Generate a List of Dates Across Multiple Date Ranges in SQL?

Generating Dates Across Multiple Date Ranges

To generate dates between two given dates is a common task in SQL. However, generating dates across multiple date ranges can present challenges.

Consider the following input table containing two date ranges:

| ID | START_DATE | END_DATE   |
|-----|------------|------------|
| 101 | April, 01 2013 | April, 10 2013 |
| 102 | May, 10 2013 | May, 12 2013 |

Our desired output would be a list of dates for each range:

| ID | Dates |
|-----|-------|
| 101 | April, 01 2013 |
| 101 | April, 02 2013 |
| 101 | April, 03 2013 |
| 101 | April, 04 2013 |
| 101 | April, 05 2013 |
| 101 | April, 06 2013 |
| 101 | April, 07 2013 |
| 101 | April, 08 2013 |
| 101 | April, 09 2013 |
| 101 | April, 10 2013 |
| 102 | May, 10 2013 |
| 102 | May, 11 2013 |
| 102 | May, 12 2013 |

To achieve this, we can employ the following SQL query:

select 
  A.ID, 
  A.START_DATE+delta dt
from 
  t_dates A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(end_date - start_date) from t_dates
     )
  )
where A.START_DATE+delta <= A.end_date
order by 1, 2

This query leverages a subquery to generate a list of date offsets from 0 to the maximum date range in the table. It then joins this subquery with the input table using the START_DATE column and checks if each offset is within the range defined by the END_DATE column. The results are sorted by ID and the generated dates (dt).

The above is the detailed content of How to Generate a List of Dates Across Multiple Date Ranges in SQL?. For more information, please follow other related articles on the PHP Chinese website!