Database Challenges

The following TSQL template can be used to perform a basic testing of your solutions for TSQL Challenge 25

/***********************************************************************
 ---------------------------------------------- 
 BASIC Testing Sandbox for TSQL Challenge 25
 Copyright © beyondrelational.com
 ---------------------------------------------- 

 Notes:
 1. Copy this template and paste in your SSMS Query editor

 2. Insert your query (solution for TSQL Challenge 25) 
    in the Section between "Insert your query below" 
    and "Insert your query above"

 3. Run the whole batch. If you see 
    "Invalid Results...Failed!" getting printed in output 
    window, there is something wrong in the query. 

 Revision History:
 Rev 00 - 07 April 2010 - Jacob Sebastian	- Initial Release
***********************************************************************/
/*
	Create a wrapper procedure around the solution
*/
IF OBJECT_ID('tempdb..#TestSolution') IS NOT NULL BEGIN
	DROP PROCEDURE #TestSolution
END
GO

CREATE PROCEDURE #TestSolution AS
BEGIN
	-- 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 NOT NULL FOREIGN KEY 
			REFERENCES TC25_Appointments(AppID),
		Recurrence VARCHAR(50),
		Pattern VARCHAR(50),
		Val TINYINT
	);


	-- Build the test data
	SET IDENTITY_INSERT TC25_Appointments ON
	INSERT INTO TC25_Appointments (
		AppID,
		TaskName,
		BeginAt,
		Duration,
		StartDate,
		EndDate )
	SELECT 1, 'Sales Meeting','10:00','00:45',
		'2010-01-01 11:00','2010-03-07 11:00 ' UNION ALL
	SELECT 2, 'Project Meeting','10:45','01:30',
		'2010-01-13 10:30','2010-03-11 14:00' UNION ALL
	SELECT 3, 'Support Meeting','12:00','01:00',
		'2010-01-11 08:00',NULL UNION ALL
	SELECT 4, 'Customer Meeting','11:25','03:00',
		'2010-01-07 10:00','2010-02-23 21:00' UNION ALL
	SELECT 5, 'Payroll Meeting','16:00','01:00',
		'2009-07-23 10:00',NULL UNION ALL
	SELECT 6, 'Budget Meeting','10:00','08:00',
		'2008-01-01 10:00',NULL   
	SET IDENTITY_INSERT TC25_Appointments OFF

	INSERT INTO TC25_Schedules (
		AppID, Recurrence, Pattern, Val
	)
	SELECT 1,'Daily','Weekdays',NULL UNION ALL
	SELECT 2,'Daily','Every N',2 UNION ALL
	SELECT 3,'Weekly','Every N',1 UNION ALL
	SELECT 4,'Weekly','Mon,Wed,Fri',NULL UNION ALL
	SELECT 5,'Monthly','First Tue Every N',1 UNION ALL
	SELECT 6,'Yearly','Third Tue Every Feb',NULL 
	;

	/* Insert your query below */

	/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/

DECLARE @x TABLE(
	AutoID INT IDENTITY,
	TaskName VARCHAR(50),
	Date VARCHAR(10),
	Start CHAR(5),
	Duration CHAR(5)
)

-- Execute the code and 
INSERT @x EXEC #TestSolution

DECLARE @z TABLE(
	AutoID INT IDENTITY,
	TaskName VARCHAR(50),
	Date VARCHAR(10),
	Start CHAR(5),
	Duration CHAR(5)
)

INSERT INTO @z(TaskName, Date, Start, Duration)
SELECT 'Project Meeting','2010-02-20','10:45','01:30' UNION ALL
SELECT 'Sales Meeting','2010-02-22','10:00','00:45' UNION ALL   
SELECT 'Project Meeting','2010-02-22','10:45','01:30' UNION ALL
SELECT 'Customer Meeting','2010-02-22','11:25','03:00' UNION ALL
SELECT 'Support Meeting','2010-02-22','12:00','01:00' UNION ALL
SELECT 'Sales Meeting','2010-02-23','10:00','00:45' UNION ALL   
SELECT 'Sales Meeting','2010-02-24','10:00','00:45' UNION ALL   
SELECT 'Project Meeting','2010-02-24','10:45','01:30' UNION ALL
SELECT 'Sales Meeting','2010-02-25','10:00','00:45' UNION ALL   
SELECT 'Sales Meeting','2010-02-26','10:00','00:45' UNION ALL   
SELECT 'Project Meeting','2010-02-26','10:45','01:30' UNION ALL
SELECT 'Project Meeting','2010-02-28','10:45','01:30'

-- Match the output with expected result.
IF EXISTS(
  (SELECT * FROM @x EXCEPT SELECT * FROM @z)
  UNION ALL
  (SELECT * FROM @z EXCEPT SELECT * FROM @x))
BEGIN
  PRINT 'Invalid Results...Failed!'
END ELSE BEGIN
  PRINT 'Congratulations...Passed!'
END