Database Challenges

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

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

-- Drop the table if exists
IF OBJECT_ID('TC25_Schedules','U') IS NOT NULL BEGIN
	DROP TABLE TC25_Schedules
END;
IF OBJECT_ID('TC25_Appointments','U') IS NOT NULL BEGIN
	DROP TABLE TC25_Appointments
END;

-- Create the source table
CREATE TABLE TC25_Appointments(
	AppID INT IDENTITY PRIMARY KEY,
	TaskName VARCHAR(100),
	BeginAt CHAR(5),
	Duration CHAR(5),
	StartDate SMALLDATETIME,
	EndDate SMALLDATETIME
);

CREATE TABLE TC25_Schedules (
	AppID INT IDENTITY FOREIGN KEY 
		REFERENCES TC25_Appointments(AppID),
	Recurrence VARCHAR(50),
	Pattern VARCHAR(50),
	Val TINYINT
);



-- Build the test data
;WITH Appointments AS(
	SELECT 'Sales Meeting' AS TaskName,
        '10:00' AS BeginAt, 
        '00:45' AS Duration,
        '2010-01-01 11:00' AS StartDate,
        '2010-03-07 11:00' AS EndDate UNION ALL
	SELECT 'Project Meeting','10:45','01:30',
		'2010-01-13 10:30','2010-03-11 14:00' UNION ALL
	SELECT 'Support Meeting','12:00','01:00',
		'2010-01-11 08:00',NULL UNION ALL
	SELECT 'Customer Meeting','11:25','03:00',
		'2010-01-07 10:00','2010-02-23 21:00' UNION ALL
	SELECT 'Payroll Meeting','16:00','01:00',
		'2009-07-23 10:00',NULL UNION ALL
	SELECT 'Budget Meeting','10:00','08:00',
		'2008-01-01 10:00',NULL   	
)
INSERT INTO TC25_Appointments (		
	TaskName,
	BeginAt,
	Duration,
	StartDate,
	EndDate )
SELECT TaskName + CONVERT(VARCHAR, N) AS TaskName,
	BeginAt,
	Duration,
	StartDate,
	EndDate 
FROM Appointments
CROSS JOIN tsqlc_tally WHERE  N BETWEEN 1 AND 1667 
	
	
;WITH Schedules AS(	
	SELECT 'Daily' AS Recurrence,
    'Weekdays' AS Pattern,
     NULL AS Val UNION ALL
	SELECT 'Daily','Every N',2 UNION ALL
	SELECT 'Weekly','Every N',1 UNION ALL
	SELECT 'Weekly','Mon,Wed,Fri',NULL UNION ALL
	SELECT 'Monthly','First Tue Every N',1 UNION ALL
	SELECT 'Yearly','Third Tue Every Feb',NULL 	
)
INSERT INTO TC25_Schedules (
	Recurrence, 
	Pattern, 
	Val
)
SELECT Recurrence, 
	Pattern, 
	Val
FROM Schedules
CROSS JOIN tsqlc_tally WHERE  N BETWEEN 1 AND 1667 
;