Recursive CTE -101

https://www.mysqltutorial.org/mysql-recursive-cte/

A Recursive Common Table Expression (CTE) in SQL serves the goal of querying hierarchical or recursive data structures, such as organizational charts, file systems, or threaded discussions, where each record contains a reference to another record within the same table. The goal is to traverse and retrieve data from these hierarchical structures efficiently and in a structured manner.

Here’s how it works:

1. Anchor Member: The CTE starts with an initial query called the “anchor member.” This query selects the base case or starting point for the recursion. It’s a non-recursive query.
2. Recursive Member: The CTE then includes a recursive query that references itself. This recursive query retrieves rows that are connected to the anchor member through some relationship.
3. Termination Condition: The recursion continues until a termination condition is met. This condition typically involves checking whether there are any more rows to retrieve or whether a certain depth or limit has been reached.

The primary goal of a Recursive CTE is to simplify the process of querying hierarchical data without having to write complex and error-prone loops or iterative code. It allows you to express the recursive logic in a more declarative SQL manner.

In the following example, we will create a table named “Numbers.” Let’s start with an initial value of 1 and display this value in the other columns as both odd and even numbers. We will set the end value to be 100. As a result, we will obtain increasing values in all three columns that do not exceed 100.

WITH NUMBERS AS(
 SELECT 1 AS NUMBER, 1 AS ODDNUMBER, 0 AS EVENNUMBER

 UNION ALL

 SELECT NUMBER+1, ODDNUMBER+2 ,EVENNUMBER+2 FROM NUMBERS
 WHERE NUMBER<=100 AND ODDNUMBER<=100 AND EVENNUMBER<=100

 )

SELECT * FROM NUMBERS

Now, let’s define two date ranges. The start date will be January 1, 2022, and the end date will be today. We will use a Recursive CTE to retrieve all the dates between these two dates.

WITH DATE_ AS(
 
 SELECT CAST('2022-01-01' AS DATE) AS Full_Date


 UNION ALL

 SELECT DATEADD(DAY, 1 , Full_Date) FROM DATE_
 WHERE Full_Date<GETDATE()

 )

SELECT 
 Full_Date,
 DATEPART(DY, Full_Date) AS [DAY OF YEAR],
 DATEPART(DD, Full_Date) AS [DAY OF MONTH],
 DATEPART(MM, Full_Date) AS [MONTH NUMBER],
 DATENAME(MM, Full_Date) [MONTH],
 DATEPART(DW, Full_Date) [DAY OF WEEK NUMBER],
 DATENAME(DW, Full_Date) [DAY OF WEEK],
 DATEPART(QQ, Full_Date) [QUARTER],
 DATEPART(YYYY, Full_Date) AS [YEAR]
FROM DATE_

We encountered an error because the number of rows exceeded the default maximum recursive limit. To resolve the error, I’m setting the maximum recursive value to 1000. Additionally, I’m defining the start and end values as variables at the beginning. This way, if there is a need to change the dates, it can be done easily.

DECLARE @StartDate DATE
DECLARE @EndDate DATE

SET @StartDate = '2022-01-01'
SET @EndDate = GETDATE();


WITH DATE_ AS(
 
 SELECT @StartDate AS Full_Date

 UNION ALL

 SELECT DATEADD(DAY, 1 , Full_Date) FROM DATE_
 WHERE Full_Date<@EndDate  
 )

SELECT 
 Full_Date,
 DATEPART(DY, Full_Date) AS [DAY OF YEAR],
 DATEPART(DD, Full_Date) AS [DAY OF MONTH],
 DATEPART(MM, Full_Date) AS [MONTH NUMBER],
 DATENAME(MM, Full_Date) [MONTH],
 DATEPART(DW, Full_Date) [DAY OF WEEK NUMBER],
 DATENAME(DW, Full_Date) [DAY OF WEEK],
 DATEPART(QQ, Full_Date) [QUARTER],
 DATEPART(YYYY, Full_Date) AS [YEAR]
FROM DATE_

OPTION(MAXRECURSION 1000)

We also use Recursive CTE in more advanced scenarios, but in this article, I’ve tried to explain it from the very basics to avoid intimidating anyone. I hope it has been a helpful content. See you in the next article!

To learn more, check the resources below 😉 :

Understanding SQL Server Recursive CTE By Practical Examples

WITH common_table_expression (Transact-SQL) - SQL Server