Skip to content
Hierarchical and Recursive Queries in SQL Server - Couse Notes by Chinmay Garg

Hierarchal and Recursive Queries in SQL Server

1. Recursion and CTEs

  1. CTE: Temporary named result set, defined within the execution scope of a single statement
  2. Manage complicated queries, can be used with SELECT, INSERT, UPDATE, DELETE statements, several CTEs can be defined with 1 WITH statement, can be combined with UNION/ JOIN
  3. Substitue a view, self-reference a table, Recursion query
  4. Mutiple CTEs using "," after each
Spinner
DataFrameavailable as
df2
variable
--Example of CTE
  1. Recursion: Recursion is the use of a procedure, subroutine (Invalid URL), function, algorithm that calls itself n times until a specific condition is met. A termination condition is required to prevent an indefinite run
Spinner
DataFrameavailable as
df1
variable
-- Live Calendar Table with Recursion
WITH calendar AS (
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) [date]
    UNION ALL 
    SELECT DATEADD(DAY, 1, date) [date] FROM calendar WHERE date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
)
SELECT date, MONTH(date) month, YEAR(date) year, DAY(date) day, EOMONTH(date) end_of_month, DATEPART(WEEKDAY, date) weekday, 
    DATEPART(WEEK, date) week,
CASE WHEN DATEPART(WEEKDAY, date) = 7 THEN 
        CASE WHEN (ROW_NUMBER() OVER (PARTITION BY DATEPART(WEEKDAY, date) ORDER BY date))%2 = 0 THEN 1 ELSE 0 END
    WHEN DATEPART(WEEKDAY, date) = 1 THEN 1 ELSE 0 END weekend, 
    DATEADD(DAY, -DATEPART(WEEKDAY, DATE)%7-1, Date) saturday_offset,
    CEILING(CAST(DAY(DATEADD(DAY, -DATEPART(WEEKDAY, DATE)%7-1, Date))/7 AS FLOAT)) sat_offset_weeknum
FROM calendar