Database Challenges

The following script generates the data required for the performance testing of TSQL Challenge 27.

/*************************************************************
 --------------------------------------------------------------
 Performance Testing Data for TSQL Challenge 27
 Copyright © beyondrelational.com
 -------------------------------------------------------------- 
 
 Notes:
 
 Revision History:
 Rev 00 - 31 July 2010 - Khyati Patel - Initial Release     
*************************************************************/

-- Drop the table if exists


IF OBJECT_ID('TC27','U') IS NOT NULL
	DROP TABLE TC27

CREATE TABLE TC27(
	SchID INT IDENTITY PRIMARY KEY,
	Patient INT,
	Nurse INT,
	dy CHAR(3),
	St CHAR(4),
	En CHAR(4)
)

;WITH Schedules AS(	
	SELECT 2000 AS Patient, 200 AS Nurse, 'Mon' AS dy, '0800' AS st,'1200' AS en UNION ALL
	SELECT 2000, 200, 'Mon', '0900','1300' UNION ALL
	SELECT 2000, 200, 'Mon', '1000','1400' UNION ALL
	SELECT 2000, 200, 'Mon', '1100','1600' UNION ALL
	SELECT 2000, 200, 'Mon', '1500','1700' UNION ALL

	SELECT 2000, 200, 'Tue', '0800','1000' UNION ALL
	SELECT 2000, 200, 'Tue', '0900','1200' UNION ALL
	SELECT 2000, 200, 'Tue', '1100','1400' UNION ALL
	SELECT 2000, 200, 'Tue', '1300','1600' UNION ALL
	SELECT 2000, 200, 'Tue', '1500','1700' UNION ALL

	SELECT 2000, 200, 'Wed', '0800','1000' UNION ALL
	SELECT 2000, 200, 'Wed', '0900','1400' UNION ALL
	SELECT 2000, 200, 'Wed', '1100','1500' UNION ALL
	SELECT 2000, 200, 'Wed', '1200','1600' UNION ALL
	SELECT 2000, 200, 'Wed', '1300','1700'
)
INSERT INTO TC27( Patient, Nurse, dy, st, en)
SELECT Patient + CONVERT(VARCHAR, N) AS Patient, 
Nurse + CONVERT(VARCHAR, N) AS Nurse,dy,st,en
FROM Schedules
CROSS JOIN tsqlc_tally WHERE  N BETWEEN 1 AND 135
;