info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
T-SQL - Recursive CTE's
by BF (Principal Consultant; Architecture; Engineering)
2016-12-09
Example 1: Recursive CTE - Generate a List - Numbers:
;WITH CTENumbers AS
(
SELECT num = 1 --Anchor Member
UNION ALL
SELECT num + 1 --Recursive Member
FROM CTENumbers
WHERE num + 1 <= 1000
)
SELECT num AS 'List of Numbers'
FROM CTENumbers
OPTION (MAXRECURSION 1000)
Example 2: Recursive CTE - Generate a List - Days of Year:
Can be used with other logic to solve a problem that requires a list of dates
DECLARE @varstartdate VARCHAR(100) = '2016-01-01'; --First Day of Year
DECLARE @varenddate VARCHAR(100) = '2016-12-31'; --Last Day of Year
;WITH CTEdates ([Date]) as
(
Select CONVERT(DATE, @varstartdate) as [Date] --Anchor Member - Start at first day of Year
Union All
Select dateadd(day, 1, [Date]) --Recursive Member - Add 1 Day to the Anchor Member
from CTEdates
where [Date] < CONVERT(DATE, @varenddate) --Stop at Last Day of Year
)
Select [Date] AS 'Days of Year' FROM
CTEdates
OPTION (MAXRECURSION 1000)
Example 3: Recursive CTE - Generate a List - Days of Current Month:
Can be used with other logic to solve a problem that requires a list of dates
WITH CTEdates ([Date]) as
(
Select DATEFROMPARTS(YEAR(Getdate()), MONTH(GETDATE()),1) as [Date]
union all
Select dateadd(day, 1, [Date])
from CTEdates
where [Date] < EOMONTH(GETDATE())
)
Select [Date]
from CTEdates
option (MAXRECURSION 100)
Example 4: Recursive CTE - Navigate a Hierarchy like Employee Manager
Data Prep:
CREATE TABLE dbo.Employees
(
EmployeeID int NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
Title nvarchar(100) NOT NULL,
DeptID int NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.Employees VALUES
(1, N'Bill', N'Gates', N'President',1000,NULL)
,(2, N'James', N'Smith', N'CEO',2000,1)
,(3, N'Bob', N'Taylor', N'VP Finance',2000,1)
,(4, N'Chris', N'Penton', N'VP Sales',2000,1)
,(5, N'Blair', N'Connor', N'UK Sales Manager',3000,4)
,(6, N'Jean', N'Tanner', N'UK Salesperson',3000,5)
,(7, N'Michelle', N'Armstrong', N'UK Salesperson',3000,5)
,(8, N'Cliff', N'Hendor', N'USA Sales Manager',4000,4)
,(9, N'Jon', N'Sittler', N'USA Salesperson',4000,8)
,(10, N'Peggy', N'Pearson', N'USA Salesperson',4000,8)
,(11, N'Nick', N'Plont', N'USA Salesperson',4000,8)
Recursive CTE:
;WITH CTEEmployeeManager (EmployeeID, Title, FirstName, LastName, ManagerID, DeptID, Level)
AS
(
--Anchor member definition (Get the Top Level - ManagerID is null - i.e. President)
SELECT e.EmployeeID, e.Title, e.FirstName, e.LastName, e.ManagerID, e.DeptID, 0 AS Level --Set Level 0
FROM dbo.Employees AS e
WHERE ManagerID IS NULL
UNION ALL
--Recursive member definition
SELECT e.EmployeeID, e.Title, e.FirstName, e.LastName, e.ManagerID, e.DeptID, Level + 1 --Increment Level (when the ManagerID changes the Level changes)
FROM dbo.Employees AS e
INNER JOIN CTEEmployeeManager AS cte
ON e.ManagerID = cte.EmployeeID
)
--Stmt that executes the CTE
SELECT EmployeeID, Title, FirstName, LastName, ManagerID, DeptID, Level
FROM CTEEmployeeManager
ORDER BY Level, DeptID ASC
GO
|
|
|
|
|
|
|
|
|
|