Click here to Skip to main content
Click here to Skip to main content

Tagged as

Using Common Table Expressions and Recursion in SQL Server 2008

, 28 Apr 2010
Rate this:
Please Sign up or sign in to vote.
By using recursion (and CROSS JOINS), you can generate data without looping or hard-coding INSERT statements into a temporary table.In the example below, you can use recursion to generate a week of scheduling data for three employees. -- Count variable, set to 7 for a week's worth of...
By using recursion (and CROSS JOINS), you can generate data without looping or hard-coding INSERT statements into a temporary table.
 
In the example below, you can use recursion to generate a week of scheduling data for three employees.
 
-- Count variable, set to 7 for a week's worth of data.
DECLARE @seed INT
SELECT @seed = 7;
 
-- Temp table 
DECLARE @Employees TABLE (emp_name VARCHAR(10),
			shift VARCHAR(11) )
 
-- Insert yucky hard-coded data. Ideally this would be from the database.
INSERT INTO @Employees
VALUES('Larry', '08:00-17:00')
 
INSERT INTO @Employees
VALUES('Curley', '17:00-01:00')
 
INSERT INTO @Employees
VALUES('Moe', '01:00-08:00');
 
Note the semicolon ";" at the end of the above statement. It is a requirement for Common Table Expressions (CTE) that the preceding statement end with a semicolon.
 
Here is the recursion code. The syntax is a SQL statement UNIONED with itself that decreases the seed value until it is reaches 1.
 
WITH RecurseTable(Id)
AS
(
SELECT @seed
 
UNION ALL
 
SELECT R.Id -1
FROM RecurseTable R
WHERE R.Id > 1
)
 
This generates an in-memory table called RecurseTable. The table has one column (Id) and is simply a series of numbers from 7 to 1.
 
Note that CTE results can only be used once in the next statement.
 
This block uses the recursive table cross joined with the employees to generate a schedule for the week for these three employees.
 
SELECT	E.emp_name, 		
		DATENAME(DW, DATEADD(D, RT.id, GETDATE())) AS work_day,
		CONVERT(VARCHAR(10), DATEADD(D, RT.id, GETDATE()), 101) AS work_date,
		E.shift
FROM RecurseTable RT
CROSS JOIN
	@Employees E
 
The CROSS JOIN causes each day (number) to repeat for each employee.
 

Larry 1
Larry 2
...
 
Curley 1
Curley 2
...
 
I use the day counter to populate dates for the week.
DATEADD(D, RT.id, GETDATE()), 101)
 
The result set looks like this:
emp_name   work_day                       work_date  shift
---------- ------------------------------ ---------- -----------
Larry      Friday                         12/25/2009 08:00-17:00
Curley     Friday                         12/25/2009 17:00-01:00
Moe        Friday                         12/25/2009 01:00-08:00
Larry      Thursday                       12/24/2009 08:00-17:00
Curley     Thursday                       12/24/2009 17:00-01:00
Moe        Thursday                       12/24/2009 01:00-08:00
Larry      Wednesday                      12/23/2009 08:00-17:00
Curley     Wednesday                      12/23/2009 17:00-01:00
Moe        Wednesday                      12/23/2009 01:00-08:00
...

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gordon Kushner
Software Developer (Senior)
United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 28 Apr 2010
Article Copyright 2009 by Gordon Kushner
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid