SQL Expand Date Range into Multiple Rows

In the realm of SQL database management, efficiently expanding a date range into multiple rows is a critical task for streamlined analysis of date-related data. This T-SQL solution employs recursive Common Table Expressions (CTE) to offer a highly efficient approach to this challenge. By leveraging this method, the intricate process of breaking down a date range into distinct and manageable rows is significantly optimized. This solution not only enhances the efficiency of handling date-related data within a SQL database but also provides a more organized and practical means of navigating and extracting valuable insights from complex temporal datasets that include information related to time or timestamps.

tip

You can use this SQL Server Fiddle to follow along and practice using Recursive CTE approach to expand date range into rows

SQL Server DB Fiddle: Expand Date Range into Multiple Rows

Create Events Table with Sample Data

To demonstrate how to expand date range into multiple rows, let's create a table named "Events" with three columns: EventId, StartDate, and EndDate.

Events Table

CREATE TABLE Events (
    EventId INT PRIMARY KEY,
    StartDate DATE,
    EndDate DATE
);

-- Inserting the first event (1-day duration)
INSERT INTO Events (EventId, StartDate, EndDate)
VALUES (1, '2024-01-14', '2024-01-14');

-- Inserting the second event (2-day duration)
INSERT INTO Events (EventId, StartDate, EndDate)
VALUES (2, '2024-02-01', '2024-02-02');

-- Inserting the third event (3-day duration)
INSERT INTO Events (EventId, StartDate, EndDate)
VALUES (3, '2024-03-01', '2024-03-03');

This script defines a table called "Events" with three columns and insert 3 sample event records.

1. EventId: An integer column that will serve as the primary key for the table.
2. StartDate: A date column to store the start date of the event.
3. EndDate: A date column to store the end date of the event.

Expand Date Range into Multiple Rows

The following query will expand each event's date range into multiple rows.

Expand Event Date Range into Multiple Rows

WITH cteExpandedEvents
AS (
   -- Anchor member
   SELECT [EventId],
          [StartDate] AS EventDate,
          [StartDate],
          [EndDate],
          0 AS Lvl
   FROM dbo.Events
   UNION ALL
   -- Recursive member
   SELECT [EventId],
          DATEADD(DAY, 1, EventDate) AS EventDate,
          [StartDate],
          [EndDate],
          Lvl + 1 AS Lvl
   FROM cteExpandedEvents
   WHERE EventDate < EndDate)
SELECT [EventId],
       [EventDate],
       [StartDate],
       [EndDate],
       [Lvl]
FROM cteExpandedEvents
ORDER BY EventId,
         EventDate

The first event will NOT be expanded into multiple rows because this event's start date and end date is the same. The second event's date range will be expanded into 2 rows because the event lasts for 2 days. The third event's date range will be expanded into 3 rows because the event lasts for 3 days.

EventId	EventDate	StartDate	EndDate	Lvl
1	2024-01-14	2024-01-14	2024-01-14	0
2	2024-02-01	2024-02-01	2024-02-02	0
2	2024-02-02	2024-02-01	2024-02-02	1
3	2024-03-01	2024-03-01	2024-03-03	0
3	2024-03-02	2024-03-01	2024-03-03	1
3	2024-03-03	2024-03-01	2024-03-03	2

Let's break down and explain how the query works:

1. CTE Definition (cteExpandedEvents):

   • Anchor Member:

     • The anchor member is the first part of the UNION ALL statement and selects the initial set of rows from the "dbo.Events" table.
     • It renames [StartDate] as EventDate.
     • Adds a column Lvl with a value of 1, representing the initial level.

   • Recursive Member:

     • The recursive member is the second part of the UNION ALL statement.
     • It references the CTE (cteExpandedEvents) itself.
     • It uses DATEADD(DAY, 1, EventDate) to increment the EventDate by one day.
     • Increments the Lvl by 1.
     • The recursive part is conditioned by WHERE EventDate < EndDate, ensuring the recursion continues until EventDate reaches or exceeds EndDate.

2. SELECT Statement:

   • Selects columns [EventId], [EventDate], [StartDate], [EndDate], and [Lvl] from the CTE.
   • The result set is ordered by EventId and EventDate.

3. Execution Flow:

   • The anchor member establishes the initial set of rows, each representing the start date of an event.
   • The recursive member appends rows to the CTE by incrementing the EventDate by one day for each recursion until the EventDate is no longer less than the EndDate.
   • The final result set includes all expanded rows for each event, representing each day within the event's duration.
   • The result set is ordered by EventId and EventDate to present a chronological view of the events.

This query effectively expands each event into multiple rows, representing each day within the event's duration, and includes the level (Lvl) to indicate the depth of recursion.

The anchor member produces the initial rows and set the Lvl value to 0.

EventId	EventDate	StartDate	EndDate	Lvl
1	2024-01-14	2024-01-14	2024-01-14	0
2	2024-02-01	2024-02-01	2024-02-02	0
3	2024-03-01	2024-03-01	2024-03-03	0

The first recursive call produces 2 rows and set the Lvl value to 1.

EventId	EventDate	StartDate	EndDate	Lvl
2	2024-02-02	2024-02-01	2024-02-02	1
3	2024-03-02	2024-03-01	2024-03-03	1

The second recursive call produces 1 row and set the Lvl value to 2.

EventId	EventDate	StartDate	EndDate	Lvl
3	2024-03-03	2024-03-01	2024-03-03	2

Finally, the 3 result sets produced were UNION ALL together to produce the final result set.

In a recursive Common Table Expression (CTE) like the one we have demonstrated, the recursive member refers to the data from the previous recursion level by referencing the CTE itself within the FROM clause. Let's take a closer look at the recursive member:

SELECT
    [EventId],
    DATEADD(DAY, 1, EventDate) AS EventDate,
    [StartDate],
    [EndDate],
    Lvl + 1 AS Lvl
FROM cteExpandedEvents
WHERE EventDate < EndDate

In this part of the query:

• FROM cteExpandedEvents: This line references the CTE cteExpandedEvents, and it means "from the result set of the CTE as it stands now."

• WHERE EventDate < EndDate: This condition ensures that the recursion continues only for rows where the current EventDate is less than the EndDate. Once this condition is no longer met, the recursion stops for that specific branch.

• DATEADD(DAY, 1, EventDate) AS EventDate: This expression takes the EventDate from the current row in the CTE and adds one day to it. This effectively increments the date, representing the next day in the sequence.

• Lvl + 1 AS Lvl: The Lvl column is incremented by 1 for each recursion level. It keeps track of the depth or level of the recursion.

So, in each recursion level:

• The condition WHERE EventDate < EndDate ensures that the recursion continues until the entire duration of the event is covered.
• The EventId, EventDate, StartDate, and EndDate columns from the previous recursion level (current state of the CTE) are used.
• The EventDate is incremented by one day to represent the next day in the sequence.
• The Lvl is incremented by 1 to track the recursion depth.

The recursive member keeps referring back to the previous recursion level's data in the CTE, allowing the expansion of each event's date range into multiple rows representing each day within its duration. This process continues until the condition is no longer met, effectively building a sequence of rows representing the timeline of each event.

Expand Date Range Longer Than 101 Days into Multiple Rows

Now let's insert a new event that has a duration of 102 days. To expand this event's date range into 102 rows, 101 recursive executions are required to achieve this.

Insert a 102-day Event to Exceed the Default Recursion Limit of 100

-- Inserting the fourth event (102-day duration)
INSERT INTO Events (EventId, StartDate, EndDate)
VALUES (4, '2024-01-01', DATEADD(DAY, 101, '2024-01-01'));

If you execute the same recursive query to expand date range into multiple rows, you will encounter the following error message.

danger

Msg 530 Level 16 State 1 Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The error message indicates that the recursion limit has been reached in your recursive Common Table Expression (CTE). By default, SQL Server imposes a limit of 100 recursive iterations to prevent infinite loops or excessively long queries.

To fix this issue, you can either increase the maximum recursion limit by adding the OPTION (MAXRECURSION n) clause to your query to increase the maximum recursion limit. Replace n with the desired maximum number of recursions. If you replace n with 0, the OPTION (MAXRECURSION 0) hint instructs SQL Server to remove or disable the recursion limit on a recursive Common Table Expression (CTE). By setting MAXRECURSION to 0, you allow the recursive CTE to continue iterating until the recursion condition is met or until there is no more data to process.

Expand Date Range into Multiple Rows. Disable Recusion Limit.

WITH cteExpandedEvents
AS (
   -- Anchor member
   SELECT [EventId],
          [StartDate] AS EventDate,
          [StartDate],
          [EndDate],
          0 AS Lvl
   FROM dbo.Events
   UNION ALL
   -- Recursive member
   SELECT [EventId],
          DATEADD(DAY, 1, EventDate) AS EventDate,
          [StartDate],
          [EndDate],
          Lvl + 1 AS Lvl
   FROM cteExpandedEvents
   WHERE EventDate < EndDate)
SELECT [EventId],
       [EventDate],
       [StartDate],
       [EndDate],
       [Lvl]
FROM cteExpandedEvents
ORDER BY EventId,
         EventDate
-- Disable the maximum recursion limit
OPTION (MAXRECURSION 0);