Using Common Table Expressions and Recursion in SQL Server 2008

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
...