Home

TSQL Challenge 25 - logic testing sandbox

Thu, 04/08/2010 - 06:23 — jacob

The following sandbox can be used to test your solutions against the 'tricky' data. 

/***********************************************************************
 ---------------------------------------------- 
 LOGIC 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. 
    (There may be some basic info as to what is missing/extra as well)

 Revision History:
 Rev 00 - 07 Apr 2010 - Seth Phelabaum - Initial Release
 Rev 01 - 09 Apr 2010 - Seth Phelabaum - Fixed an potential issue with 
		sequencing due to multiple appointments starting at the same time
		Added a bit better information if there are failures 
		(Still a lot that could be done here)
***********************************************************************/
/*
	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 source tables, if exist
	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 tables
	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
	);

	-- Populate the source tables
	SET IDENTITY_INSERT TC25_Appointments ON
	INSERT INTO TC25_Appointments(AppID,TaskName,BeginAt,Duration,StartDate,EndDate)
	SELECT 29, 'More Beer', '10:00', '03:00' ,'02/21/2010', '02/25/2010' 
	UNION ALL
	SELECT 24, 'AA Meeting', '10:01', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 25, 'AA Meeting 2', '10:02', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 26, 'AA Meeting 3', '10:03', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 28, 'BEER ', '10:31', '03:00' ,'02/19/2010', '03/18/2010'
	UNION ALL
	SELECT 21, 'Beer Drinking Meeting', '10:32', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 10, 'Computer Training', '10:04', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 6, 'Customer Meeting', '10:05', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 7, 'Customer Meeting 2', '10:06', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 27, 'Drinking Beer Meeting', '10:07', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 19, 'Floor Cleaning Training', '10:33', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 9, 'Forklift Training', '10:08', '24:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 5, 'General Appt', '10:09', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 3, 'HR Meeting', '10:10', '28:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 47, 'Long Lost Meeting', '10:11', '02:00' ,'02/25/2005', '02/26/2010' 
	UNION ALL
	SELECT 22, 'Meeting about Beer', '10:12', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 23, 'Meeting about Drinking Beer', '00:00', '03:00' ,'02/21/2010', '02/25/2010' 
	UNION ALL
	SELECT 18, 'Meeting about Meetings', '10:13', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 4, 'Office Meeting', '10:14', '30:00' ,'02/21/2010', '2010/02/23 09:59:00' 
	UNION ALL
	SELECT 13, 'Office Politeness Meeting', '10:15', '03:00' ,'05/21/2009', '03/18/2010' 
	UNION ALL
	SELECT 14, 'Political Correctness Training', '10:16', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 33, 'Repeating Meeting', '08:45', '00:10' ,'02/25/2010', '03/18/2010' 
	UNION ALL
	SELECT 34, 'Repeating Meeting', '09:00', '00:10' ,'02/20/2010', '03/18/2010' 
	UNION ALL
	SELECT 45, 'Repeating Meeting', '12:15', '10:00' ,'02/21/2009', '03/18/2010' 
	UNION ALL
	SELECT 46, 'Repeating Meeting', '12:30', '08:00' ,'02/21/2009', '03/18/2010' 
	UNION ALL
	SELECT 1, 'Sales Meeting', '10:17', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 8, 'Sales Meeting 2', '10:18', '23:59' ,'02/21/2010', '02/28/2010' 
	UNION ALL
	SELECT 15, 'Sample Data Creation Training', '10:19', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 12, 'Sexual Harassment Meeting', '10:20', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 16, 'Tally Table Instruction', '10:21', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 2, 'Training', '10:22', '26:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 20, 'TSQL Challenges Meeting', '10:00', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 11, 'Typing Training', '10:23', '03:00' ,'02/21/2010', '03/18/2010' 
	UNION ALL
	SELECT 17, 'Useless Meeting', '10:24', '03:00' ,'02/21/2010', '03/18/2010'					
	SET IDENTITY_INSERT TC25_Appointments OFF

	INSERT INTO TC25_Schedules(AppID, Recurrence, Pattern, Val)	
	SELECT 29, 'Yearly', 'Fourth Thu Every Feb', NULL UNION ALL
	SELECT 24, 'Weekly', 'Tue', 13 UNION ALL
	SELECT 25, 'Yearly', 'Every N', 1 UNION ALL
	SELECT 26, 'Yearly', 'Every N', 255 UNION ALL
	SELECT 28, 'Yearly', 'Third Sat Every Feb', NULL UNION ALL
	SELECT 21, 'Monthly', 'Last Wed Every N', 1 UNION ALL
	SELECT 10, 'Weekly', 'Every N', 255 UNION ALL
	SELECT 6, 'Daily', 'Every N', 80 UNION ALL
	SELECT 7, 'Daily', 'Weekends', 10 UNION ALL
	SELECT 27, 'Yearly', 'Fourth Sat Every Feb', NULL UNION ALL
	SELECT 19, 'Monthly', 'Fourth Sat Every N', 1 UNION ALL
	SELECT 9, 'Daily', 'Every N', 1 UNION ALL
	SELECT 5, 'Daily', 'Every N', 35 UNION ALL
	SELECT 3, 'Daily', 'Every N', 1 UNION ALL
	SELECT 47, 'Yearly', 'Every N', 5 UNION ALL
	SELECT 22, 'Daily', 'Every N', 255 UNION ALL
	SELECT 23, 'Monthly', 'Fourth Thu Every N', 1 UNION ALL
	SELECT 18, 'Monthly', 'Fourth Wed Every N', 1 UNION ALL
	SELECT 4, 'Monthly', 'Fourth Wed Every N', 1 UNION ALL
	SELECT 13, 'Weekly', 'Every N', 40 UNION ALL
	SELECT 14, 'Monthly', 'Every N', 1 UNION ALL
	SELECT 33, 'Daily ', 'Every N', 1 UNION ALL
	SELECT 45, 'Daily ', 'Every N', 1 UNION ALL
	SELECT 46, 'Daily ', 'Every N', 1 UNION ALL
	SELECT 1, 'Daily', 'Weekdays', 2 UNION ALL
	SELECT 8, 'Daily', 'Weekends', NULL UNION ALL
	SELECT 15, 'Monthly', 'Every N', 1 UNION ALL
	SELECT 12, 'Weekly', 'Wed', 13 UNION ALL
	SELECT 16, 'Monthly', 'First Sun Every N', 1 UNION ALL
	SELECT 2, 'Daily', 'Weekdays', NULL UNION ALL
	SELECT 20, 'Monthly', 'Last Sun Every N', 1 UNION ALL
	SELECT 11, 'Weekly', 'Tue', NULL UNION ALL
	SELECT 17, 'Monthly', 'Second Tue Every N', 1
	;
	
	/* 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(100),
	Date VARCHAR(10),
	Start CHAR(5),
	Duration CHAR(5)
)

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

-- Table to hold the expected results
DECLARE @z TABLE(
	AutoID INT,
	TaskName VARCHAR(100),
	Date VARCHAR(10),
	Start CHAR(5),
	Duration CHAR(5)
)

-- Build the expected results
INSERT INTO @z(AutoID, TaskName, Date, Start, Duration)
SELECT 1, N'BEER ', '2010-02-20', N'10:31', N'03:00' 
UNION ALL
SELECT 2, N'Repeating Meeting', '2010-02-20', N'12:15', N'10:00' 
UNION ALL
SELECT 3, N'Repeating Meeting', '2010-02-20', N'12:30', N'08:00' 
UNION ALL
SELECT 4, N'AA Meeting 2', '2010-02-21', N'10:02', N'03:00' 
UNION ALL
SELECT 5, N'AA Meeting 3', '2010-02-21', N'10:03', N'03:00' 
UNION ALL
SELECT 6, N'Computer Training', '2010-02-21', N'10:04', N'03:00' 
UNION ALL
SELECT 7, N'Customer Meeting', '2010-02-21', N'10:05', N'03:00' 
UNION ALL
SELECT 8, N'Customer Meeting 2', '2010-02-21', N'10:06', N'03:00' 
UNION ALL
SELECT 9, N'Forklift Training', '2010-02-21', N'10:08', N'24:00' 
UNION ALL
SELECT 10, N'General Appt', '2010-02-21', N'10:09', N'03:00' 
UNION ALL
SELECT 11, N'HR Meeting', '2010-02-21', N'10:10', N'28:00' 
UNION ALL
SELECT 12, N'Meeting about Beer', '2010-02-21', N'10:12', N'03:00' 
UNION ALL
SELECT 13, N'Political Correctness Training', '2010-02-21', N'10:16', N'03:00' 
UNION ALL
SELECT 14, N'Sales Meeting 2', '2010-02-21', N'10:18', N'23:59' 
UNION ALL
SELECT 15, N'Sample Data Creation Training', '2010-02-21', N'10:19', N'03:00' 
UNION ALL
SELECT 16, N'Repeating Meeting', '2010-02-21', N'12:15', N'10:00' 
UNION ALL
SELECT 17, N'Repeating Meeting', '2010-02-21', N'12:30', N'08:00' 
UNION ALL
SELECT 18, N'Forklift Training', '2010-02-22', N'10:08', N'24:00' 
UNION ALL
SELECT 19, N'HR Meeting', '2010-02-22', N'10:10', N'28:00' 
UNION ALL
SELECT 20, N'Sales Meeting', '2010-02-22', N'10:17', N'03:00' 
UNION ALL
SELECT 21, N'Training', '2010-02-22', N'10:22', N'26:00' 
UNION ALL
SELECT 22, N'Repeating Meeting', '2010-02-22', N'12:15', N'10:00' 
UNION ALL
SELECT 23, N'Repeating Meeting', '2010-02-22', N'12:30', N'08:00' 
UNION ALL
SELECT 24, N'AA Meeting', '2010-02-23', N'10:01', N'03:00' 
UNION ALL
SELECT 25, N'Forklift Training', '2010-02-23', N'10:08', N'24:00' 
UNION ALL
SELECT 26, N'HR Meeting', '2010-02-23', N'10:10', N'28:00' 
UNION ALL
SELECT 27, N'Sales Meeting', '2010-02-23', N'10:17', N'03:00' 
UNION ALL
SELECT 28, N'Training', '2010-02-23', N'10:22', N'26:00' 
UNION ALL
SELECT 29, N'Typing Training', '2010-02-23', N'10:23', N'03:00' 
UNION ALL
SELECT 30, N'Repeating Meeting', '2010-02-23', N'12:15', N'10:00' 
UNION ALL
SELECT 31, N'Repeating Meeting', '2010-02-23', N'12:30', N'08:00' 
UNION ALL
SELECT 32, N'Forklift Training', '2010-02-24', N'10:08', N'24:00' 
UNION ALL
SELECT 33, N'HR Meeting', '2010-02-24', N'10:10', N'28:00' 
UNION ALL
SELECT 34, N'Meeting about Meetings', '2010-02-24', N'10:13', N'03:00' 
UNION ALL
SELECT 35, N'Sales Meeting', '2010-02-24', N'10:17', N'03:00' 
UNION ALL
SELECT 36, N'Sexual Harassment Meeting', '2010-02-24', N'10:20', N'03:00' 
UNION ALL
SELECT 37, N'Training', '2010-02-24', N'10:22', N'26:00' 
UNION ALL
SELECT 38, N'Beer Drinking Meeting', '2010-02-24', N'10:32', N'03:00' 
UNION ALL
SELECT 39, N'Repeating Meeting', '2010-02-24', N'12:15', N'10:00' 
UNION ALL
SELECT 40, N'Repeating Meeting', '2010-02-24', N'12:30', N'08:00' 
UNION ALL
SELECT 41, N'Meeting about Drinking Beer', '2010-02-25', N'00:00', N'03:00' 
UNION ALL
SELECT 42, N'Repeating Meeting', '2010-02-25', N'08:45 ', N'00:10' 
UNION ALL
SELECT 43, N'Forklift Training', '2010-02-25', N'10:08', N'24:00' 
UNION ALL
SELECT 44, N'HR Meeting', '2010-02-25', N'10:10', N'28:00' 
UNION ALL
SELECT 45, N'Long Lost Meeting', '2010-02-25', N'10:11', N'02:00' 
UNION ALL
SELECT 46, N'Office Politeness Meeting', '2010-02-25', N'10:15', N'03:00' 
UNION ALL
SELECT 47, N'Sales Meeting', '2010-02-25', N'10:17', N'03:00' 
UNION ALL
SELECT 48, N'Training', '2010-02-25', N'10:22', N'26:00' 
UNION ALL
SELECT 49, N'Repeating Meeting', '2010-02-25', N'12:15', N'10:00' 
UNION ALL
SELECT 50, N'Repeating Meeting', '2010-02-25', N'12:30', N'08:00' 
UNION ALL
SELECT 51, N'Repeating Meeting', '2010-02-26', N'08:45 ', N'00:10' 
UNION ALL
SELECT 52, N'Forklift Training', '2010-02-26', N'10:08', N'24:00' 
UNION ALL
SELECT 53, N'HR Meeting', '2010-02-26', N'10:10', N'28:00' 
UNION ALL
SELECT 54, N'Sales Meeting', '2010-02-26', N'10:17', N'03:00' 
UNION ALL
SELECT 55, N'Training', '2010-02-26', N'10:22', N'26:00' 
UNION ALL
SELECT 56, N'Repeating Meeting', '2010-02-26', N'12:15', N'10:00' 
UNION ALL
SELECT 57, N'Repeating Meeting', '2010-02-26', N'12:30', N'08:00' 
UNION ALL
SELECT 58, N'Repeating Meeting', '2010-02-27', N'08:45 ', N'00:10' 
UNION ALL
SELECT 59, N'Customer Meeting 2', '2010-02-27', N'10:06', N'03:00' 
UNION ALL
SELECT 60, N'Drinking Beer Meeting', '2010-02-27', N'10:07', N'03:00' 
UNION ALL
SELECT 61, N'Forklift Training', '2010-02-27', N'10:08', N'24:00' 
UNION ALL
SELECT 62, N'HR Meeting', '2010-02-27', N'10:10', N'28:00' 
UNION ALL
SELECT 63, N'Sales Meeting 2', '2010-02-27', N'10:18', N'23:59' 
UNION ALL
SELECT 64, N'Floor Cleaning Training', '2010-02-27', N'10:33', N'03:00' 
UNION ALL
SELECT 65, N'Repeating Meeting', '2010-02-27', N'12:15', N'10:00' 
UNION ALL
SELECT 66, N'Repeating Meeting', '2010-02-27', N'12:30', N'08:00' 
UNION ALL
SELECT 67, N'Repeating Meeting', '2010-02-28', N'08:45 ', N'00:10' 
UNION ALL
SELECT 68, N'TSQL Challenges Meeting', '2010-02-28', N'10:00', N'03:00' 
UNION ALL
SELECT 69, N'Customer Meeting 2', '2010-02-28', N'10:06', N'03:00' 
UNION ALL
SELECT 70, N'Forklift Training', '2010-02-28', N'10:08', N'24:00' 
UNION ALL
SELECT 71, N'HR Meeting', '2010-02-28', N'10:10', N'28:00' 
UNION ALL
SELECT 72, N'Repeating Meeting', '2010-02-28', N'12:15', N'10:00' 
UNION ALL
SELECT 73, N'Repeating Meeting', '2010-02-28', N'12:30', N'08:00'

SELECT * FROM @z order by date, start

-- 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!'
	IF EXISTS  (  -- Show Missing/Extra Rows
	  SELECT TaskName, [Date], Start, Duration FROM @x 
	  EXCEPT 
	  SELECT TaskName, [Date], Start, Duration FROM @z
	  UNION ALL
	  SELECT TaskName, [Date], Start, Duration FROM @z 
	  EXCEPT 
	  SELECT TaskName, [Date], Start, Duration FROM @x
	) 
	BEGIN
		SELECT TaskName, [Date], Start, Duration,'Extra' FROM @x 
		EXCEPT 
		SELECT TaskName, [Date], Start, Duration,'Extra' FROM @z
		UNION ALL
		SELECT TaskName, [Date], Start, Duration,'Missing' FROM @z 
		EXCEPT 
		SELECT TaskName, [Date], Start, Duration, 'Missing' FROM @x
	END ELSE BEGIN -- If No Missing/Extra rows, show Sequencing Errors.
		SELECT 
			*, 'Sequence'
		FROM (
			SELECT * FROM @x EXCEPT SELECT * FROM @z
			UNION ALL
			SELECT * FROM @z EXCEPT SELECT * FROM @x
		) A
	END
END ELSE BEGIN
  PRINT 'Congratulations...Passed!'
END