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
- CTE: Temporary named result set, defined within the execution scope of a single statement
- Manage complicated queries, can be used with
SELECT, INSERT, UPDATE, DELETE
statements, several CTEs can be defined with 1WITH
statement, can be combined withUNION/ JOIN
- Substitue a view, self-reference a table, Recursion query
- Mutiple CTEs using "," after each
DataFrameavailable as
df2
variable
--Example of CTE
- 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
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