Database Challenges

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

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

IF OBJECT_ID('TC24_Schedules','U') IS NOT NULL BEGIN
	DROP TABLE TC24_Schedules
END
;
GO

CREATE TABLE TC24_Schedules (
	SchID INT IDENTITY PRIMARY KEY,
	EmpName VARCHAR(50),
	Activity VARCHAR(15),
	StartTime SMALLDATETIME,
	Duration VARCHAR(5)
)
;


IF OBJECT_ID('TC24_ActivityLog','U') IS NOT NULL BEGIN
	DROP TABLE TC24_ActivityLog
END
;

CREATE TABLE TC24_ActivityLog (
	LogID INT IDENTITY PRIMARY KEY,
	EmpName VARCHAR(50),
	Activity VARCHAR(15),
	StartTime SMALLDATETIME,
	Duration VARCHAR(5)
)
;


;WITH Schedules AS (
	SELECT 
		'John' AS EmpName, 
		'HR Meeting' AS Activity, 
		'2010-01-01 10:15:00' AS StartTime, 
		'01:30' AS Duration 
	UNION ALL
	SELECT 'John', 'Lunch', '2010-01-01 13:00:00', '00:45' UNION ALL
	SELECT 'John', 'Training', '2010-01-01 15:00:00', '01:00' UNION ALL
	SELECT 'Mike', 'HR Meeting', '2010-01-01 10:15:00', '01:30' UNION ALL
	SELECT 'Mike', 'Lunch', '2010-01-01 13:00:00', '00:45' UNION ALL
	SELECT 'Jessica', 'Training', '2010-01-01 11:20:00', '00:30' 
)
INSERT INTO TC24_Schedules (EmpName, Activity, StartTime, Duration)
SELECT 
	EmpName + CONVERT(VARCHAR, N) AS EmpName,
	Activity, 
	StartTime,
	Duration	
FROM Schedules
CROSS JOIN tsqlc_tally WHERE  N BETWEEN 1 AND 1667

;WITH ActivityLog AS (	
	SELECT 
		'John' AS EmpName, 
		'HR Meeting' AS Activity, 
		'2010-01-01 10:00:00' AS StartTime, 
		'00:15' AS Duration
	UNION ALL
	SELECT 'John', 'HR Meeting', '2010-01-01 10:30:00', '00:30' 
	UNION ALL
	SELECT 'John', 'HR Meeting', '2010-01-01 11:00:00', '00:30' 
	UNION ALL
	SELECT 'John', 'HR Meeting', '2010-01-01 11:30:00', '00:15' 
	UNION ALL
	SELECT 'John', 'Lunch', '2010-01-01 13:00:00', '00:30' 
	UNION ALL
	SELECT 'John', 'Lunch', '2010-01-01 13:30:00', '00:15' 
	UNION ALL
	SELECT 'John', 'Training', '2010-01-01 15:00:00', '00:30' 
	UNION ALL
	SELECT 'John', 'Training', '2010-01-01 15:30:00', '00:20' 
	UNION ALL
	SELECT 'Mike', 'HR Meeting', '2010-01-01 10:00:00', '00:15' 
	UNION ALL
	SELECT 'Mike', 'HR Meeting', '2010-01-01 10:30:00', '00:30' 
	UNION ALL
	SELECT 'Mike', 'HR Meeting', '2010-01-01 11:00:00', '00:30' 
	UNION ALL
	SELECT 'Mike', 'HR Meeting', '2010-01-01 11:30:00', '00:10' 
	UNION ALL
	SELECT 'Mike', 'Lunch', '2010-01-01 13:00:00', '00:25' 
	UNION ALL
	SELECT 'Mike', 'Lunch', '2010-01-01 13:30:00', '00:25' 
	UNION ALL
	SELECT 'Jessica', 'Training', '2010-01-01 11:00:00', '00:05' 
	UNION ALL
	SELECT 'Jessica', 'Training', '2010-01-01 11:30:00', '00:20' 
)
INSERT INTO TC24_ActivityLog(EmpName, Activity, StartTime, Duration)
SELECT 
	EmpName + CONVERT(VARCHAR, N) AS EmpName, 
	Activity, 
	StartTime, 
	Duration
FROM ActivityLog
CROSS JOIN tsqlc_tally WHERE  N BETWEEN 1 AND 625