Skip to main content

SQL Expanding Date Range into Rows Using Recursive and Cursor Approaches

I frequently encounter a common scenario where I must expand a date range into multiple rows within a SQL table. To illustrate, let's consider a scenario where an event's date period is recorded as a single row with a From Date of 2023-11-15 and a To Date of 2023-11-17. In this case, the objective is to convert this single row into 3 separate rows, each representing a single day of event.

I have seen developers solving this with different approaches. In this post, we will focus on discussing 2 common SQL solutions (CTE Recursive and While Loop Cursor) frequently used by developers to convert row with date range to many rows with each date.

Sample Data Setup​

The provided SQL script consists of two main parts: creating a table named "Events" and populating it with two records. Let's break down these parts:

Create the Events table and populate the table with two records
-- Create the Events table
CREATE TABLE Events (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(255),
    FromDate DATE,
    ToDate DATE
);

-- Populate the table with two records
INSERT INTO Events (Name, FromDate, ToDate)
VALUES ('Event 1', '2023-11-10', '2023-11-12'),
       ('Event 2', '2023-11-15', '2023-11-17');

Events Table

IDNameFromDateToDate
1Event 12023-11-102023-11-11
2Event 22023-11-152023-11-17

  1. CREATE TABLE Events: This SQL statement is used to create a table named "Events" with the following columns:

    • ID: An integer column with the IDENTITY(1,1) property, which means it will automatically generate unique values starting from 1 for each new record. The ID column is also defined as the primary key of the table, ensuring each record has a unique identifier.
    • Name: A column to store the name of the event. It is defined as an NVARCHAR data type with a maximum length of 255 characters.
    • FromDate: A column to store the start date of the event. It is defined as a DATE data type.
    • ToDate: A column to store the end date of the event. It is also defined as a DATE data type.

This part of the script defines the structure of the "Events" table, specifying the data types for each column and setting the primary key for the table.

  1. INSERT INTO Events (Name, FromDate, ToDate) VALUES ...: This part of the script inserts two records into the "Events" table. Each record represents an event and consists of the following information:

    • Event 1:

      • Name: 'Event 1'
      • FromDate: '2023-11-10'
      • ToDate: '2023-11-11'

    • Event 2:

      • Name: 'Event 2'
      • FromDate: '2023-11-15'
      • ToDate: '2023-11-17'

These INSERT statements populate the "Events" table with data for two distinct events. The "Name" column stores the event name, while the "FromDate" and "ToDate" columns store the start and end dates of each event.

Overall, this script sets up the "Events" table with the necessary structure and inserts two initial records, allowing you to store and manage event data within the database.

We will use these 2 sample rows to demonstrate how to expand each event row into multiple event rows each representing a single day of event. Event 1 will be expanded into 2 rows. Event 2 will be expanded into 3 rows.

Expand Date Ranges into Rows: Recursive CTE​

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

The SQL script below demonstrates a Recursive CTE (Common Table Expression) used to expand date ranges for events.

tip

You can add the OPTION (MAXRECURSION 0) clause to the end of a CTE definition to specify that there is no limit on the number of recursion levels, effectively allowing the query to continue until it either reaches the termination condition specified in the query or until the server runs out of resources. Even though this option is flexible for supporting expansion of any event date range, it's NOT recommeneded as it could exhaust the server if there are multiple bad records with huge date range (e.g. 2023-11-10 to 9999-12-31).

You can replace the 0 with any positive integer to limit the number of recursion levels. For example, OPTION (MAXRECURSION 100) would limit the recursion to 100 levels and this is the DEFAULT if you do NOT specify this OPTION clause.

If you know that some event records' date range will exceed 101 days, but not exceed 365 days, you can set the recursion dpeth to 364 such as OPTION (MAXRECURSION 364). It is best practice to set an upper limit of recursion depth. This is what we have done in the code snippet below.

Expand Date Ranges into Rows with Recursive CTE
-- Common Table Expression (CTE) to expand date ranges
WITH EventsExpanded
AS (
	SELECT ID
		,Name
		,FromDate AS EventDate
		,FromDate
		,ToDate
	FROM Events

	UNION ALL

	SELECT ID
		,Name
		,DATEADD(DAY, 1, EventDate)
		,FromDate
		,ToDate
	FROM EventsExpanded
	WHERE DATEADD(DAY, 1, EventDate) <= ToDate
	)
SELECT ID
	,Name
	,EventDate
	,FromDate
	,ToDate
FROM EventsExpanded
ORDER BY ID
	,EventDate
-- Recursion depth 364 will support maximum event date range of 365 days
OPTION (MAXRECURSION 364);

Each Event Record Expanded into Multiple Rows

IDNameEventDateFromDateToDate
1Event 12023-11-102023-11-102023-11-11
1Event 12023-11-112023-11-102023-11-11
2Event 22023-11-152023-11-152023-11-17
2Event 22023-11-162023-11-152023-11-17
2Event 22023-11-172023-11-152023-11-17

Let's go over the script step by step:

  1. WITH EventsExpanded AS (...): This is the beginning of the CTE definition. It starts by declaring a CTE named "EventsExpanded." The CTE allows you to create a temporary result set that can be referred to within the subsequent SQL statements.

  2. (SELECT ID, Name, FromDate AS EventDate, FromDate, ToDate FROM Events): This is the initial part of the CTE. It selects data from the "Events" table and provides the initial set of rows. It renames the "FromDate" column as "EventDate" and includes the original "FromDate" and "ToDate" columns.

  3. UNION ALL: This clause is used to combine the results of the two SELECT statements within the CTE. It allows you to stack rows from multiple queries on top of each other, effectively expanding the result set.

  4. (SELECT ID, Name, DATEADD(DAY, 1, EventDate), FromDate, ToDate FROM EventsExpanded WHERE DATEADD(DAY, 1, EventDate) <= ToDate): This part of the CTE is recursive. It selects data from the "EventsExpanded" CTE, which references itself. It takes the previous "EventDate" and increments it by one day using DATEADD. The recursive part is executed as long as the condition "DATEADD(DAY, 1, EventDate) <= ToDate" is met. This ensures that the recursion stops when the expanded date range reaches the "ToDate."

  5. SELECT ... FROM EventsExpanded: After the CTE is defined, this part of the script selects data from the "EventsExpanded" CTE. It retrieves the ID, Name, EventDate, FromDate, and ToDate columns from the CTE.

  6. ORDER BY ID, EventDate: The selected data is ordered by the event's ID and the expanded date ("EventDate"). This ordering ensures that the results are presented in a meaningful sequence.

The CTE allows for a concise and declarative way to achieve the desired result, expanding date ranges for events. It's a set-based approach that leverages recursive capabilities in SQL, providing a clear and efficient solution for the task.

Expand Date Range into Rows: While Loop Cursor​

You can use this Fiddle to follow along and practice using While Loop Cursor approach to expand date range into rows

The SQL script below demonstrates how to use a cursor and a temporary table to expand date ranges for events.

-- Create a temporary table to store the expanded events
CREATE TABLE #ExpandedEvents (
	ID INT
	,Name NVARCHAR(255)
	,EventDate DATE
	,FromDate DATE
	,ToDate DATE
	);

-- Declare variables for cursor and looping
DECLARE @EventID INT;
DECLARE @EventName NVARCHAR(255);
DECLARE @FromDate DATE;
DECLARE @ToDate DATE;
DECLARE @CurrentDate DATE;

-- Declare a cursor to fetch event data
DECLARE EventCursor CURSOR
FOR
SELECT ID
	,Name
	,FromDate
	,ToDate
FROM Events;

-- Open the cursor
OPEN EventCursor;

-- Initialize the variables for the first row
FETCH NEXT
FROM EventCursor
INTO @EventID
	,@EventName
	,@FromDate
	,@ToDate;

-- Loop through the events
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @CurrentDate = @FromDate;

	-- Expand the date range and insert into the temporary table
	WHILE @CurrentDate <= @ToDate
	BEGIN
		INSERT INTO #ExpandedEvents (
			ID
			,Name
			,EventDate
			,FromDate
			,ToDate
			)
		VALUES (
			@EventID
			,@EventName
			,@CurrentDate
			,@FromDate
			,@ToDate
			);

		-- Increment the current date
		SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
	END;

	-- Fetch the next event
	FETCH NEXT
	FROM EventCursor
	INTO @EventID
		,@EventName
		,@FromDate
		,@ToDate;
END;

-- Close and deallocate the cursor
CLOSE EventCursor;

DEALLOCATE EventCursor;

-- Select the expanded events from the temporary table
SELECT ID
	,Name
	,EventDate
	,FromDate
	,ToDate
FROM #ExpandedEvents
ORDER BY ID
	,EventDate;

-- Drop the temporary table
DROP TABLE #ExpandedEvents;

Each Event Record Expanded into Multiple Rows

IDNameEventDateFromDateToDate
1Event 12023-11-102023-11-102023-11-11
1Event 12023-11-112023-11-102023-11-11
2Event 22023-11-152023-11-152023-11-17
2Event 22023-11-162023-11-152023-11-17
2Event 22023-11-172023-11-152023-11-17

Let's go over the script step by step:

  1. CREATE TABLE #ExpandedEvents: This SQL statement creates a temporary table named "#ExpandedEvents" to store the expanded events. The temporary table has columns for:

    • ID: An integer column to store the event's ID.
    • Name: A column to store the name of the event (as an NVARCHAR string with a maximum length of 255 characters).
    • EventDate: A column to store the individual dates within the event's date range.
    • FromDate: A column to store the start date of the event.
    • ToDate: A column to store the end date of the event.

  2. DECLARE @EventID INT; DECLARE @EventName NVARCHAR(255); DECLARE @FromDate DATE; DECLARE @ToDate DATE; DECLARE @CurrentDate DATE;: These lines declare variables that will be used to hold various values during the processing, such as event details and date information.

  3. DECLARE EventCursor CURSOR FOR SELECT ID, Name, FromDate, ToDate FROM Events;: This SQL statement declares a cursor named "EventCursor" to fetch data from the "Events" table. The cursor will be used to iterate through each event in the "Events" table.

  4. OPEN EventCursor;: This line opens the cursor, allowing it to start fetching rows from the result set obtained by the cursor's query.

  5. FETCH NEXT FROM EventCursor INTO @EventID, @EventName, @FromDate, @ToDate;: This line fetches the first row of data from the cursor's result set and assigns the values to the declared variables. This initialization is done before entering the main loop.

  6. WHILE @@FETCH_STATUS = 0 BEGIN ... END;: This is the main loop of the script. It continues as long as the @@FETCH_STATUS is 0, which indicates that there are more rows to fetch from the cursor's result set.

  7. SET @CurrentDate = @FromDate;: This line initializes the @CurrentDate variable with the starting date of the event.

  8. WHILE @CurrentDate <= @ToDate BEGIN ... END;: Inside the main loop, there is a nested loop that iterates over dates from @FromDate to @ToDate. It inserts the event details into the temporary table for each date.

  9. INSERT INTO #ExpandedEvents ...: This line inserts a row into the temporary table "#ExpandedEvents" for each date within the event's date range.

  10. SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);: After inserting a row for the current date, this line increments the date by one day in preparation for the next iteration.

  11. FETCH NEXT FROM EventCursor INTO @EventID, @EventName, @FromDate, @ToDate;: After processing one event, this line fetches the next event from the cursor's result set to continue the loop.

  12. CLOSE EventCursor;: After looping through all the events, this line closes the cursor.

  13. DEALLOCATE EventCursor;: This line deallocates the cursor, freeing up resources.

  14. SELECT ... FROM #ExpandedEvents: This SQL statement retrieves the expanded events from the temporary table "#ExpandedEvents" and orders them by ID and EventDate.

  15. DROP TABLE #ExpandedEvents;: Finally, this line drops the temporary table to clean up and remove the temporary data.

This script is a procedural way to achieve the same result as the Common Table Expression (CTE) approach, and it's typically used when more granular control over the process is required or when dealing with complex logic that may not be easily expressed using a CTE.

Conclusion​

Both the Common Table Expression (CTE) recursive approach and the While Loop Cursor approach serve the same purpose of expanding date ranges for events in SQL, but they have different characteristics and considerations. Let's summarize the key points of each approach and discuss which one might be better based on certain criteria.

CTE Recursive Approach:

  • The CTE approach is more declarative and set-based. It leverages the power of recursive queries in SQL.
  • It's generally more concise and easier to read, making it a preferred choice when code clarity is a priority.
  • It's easier to implement and understand for those familiar with SQL's recursive capabilities.
  • It is well-suited for simple to moderately complex data manipulation tasks.

While Loop and Cursor Approach:

  • The While Loop and Cursor approach is more procedural and imperative in nature. It requires explicitly declaring variables and using looping constructs.
  • It may be a better choice when dealing with very complex or customized logic that cannot be easily expressed using a CTE.
  • It allows for more fine-grained control over each step of the process, which can be useful for specialized scenarios.
  • It may be more familiar to developers with a procedural programming background.

Which Approach is Better?

The choice between the two approaches depends on the specific requirements of your task and the characteristics of your data. Here are some considerations to help you decide:

  1. Complexity of Logic: If your task involves complex data manipulation, custom conditions, or intricate calculations, the While Loop and Cursor approach provides more flexibility and control. It can handle scenarios where the logic for expanding date ranges is not easily expressed using a recursive CTE.

  2. Code Clarity: For straightforward tasks and scenarios where code clarity and readability are important, the CTE approach is often preferred. It provides a more elegant and concise way to express recursive operations.

  3. Performance: Depending on the database system and the volume of data, performance may vary between the two approaches. In some cases, the While Loop approach may perform better due to its more procedural nature, while in other cases, the CTE approach may be optimized by the database engine.

  4. Familiarity: Consider the skillset of your development team. If your team is more comfortable with one approach over the other, that might influence your choice.

In summary, there is no one-size-fits-all answer to which approach is better. It depends on the specific requirements and constraints of your task. Both approaches have their strengths and weaknesses, and the choice should be made based on the nature of the problem you are trying to solve and the preferences and skills of your development team.