Database Challenges

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

/***********************************************************************
 ---------------------------------------------- 
 LOGIC Testing Sandbox for TSQL Challenge 27
 Copyright © beyondrelational.com
 ---------------------------------------------- 

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

 2. Insert your query (solution for TSQL Challenge 27) 
    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 - 29 July 2010 - Khyati - 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

	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)
	)

	INSERT INTO TC27( Patient, Nurse, dy, st, en)
	SELECT 2000, 201, 'Mon', '0800','1200' UNION ALL
	SELECT 2000, 201, 'Mon', '0900','1300' UNION ALL
	SELECT 2000, 201, 'Mon', '1000','1400' UNION ALL
	SELECT 2000, 201, 'Mon', '1100','1600' UNION ALL
	SELECT 2000, 201, 'Mon', '1500','1700' UNION ALL

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

	SELECT 2000, 201, 'Wed', '0800','1000' UNION ALL
	SELECT 2000, 201, 'Wed', '0900','1400' UNION ALL
	SELECT 2000, 201, 'Wed', '1100','1500' UNION ALL
	SELECT 2000, 201, 'Wed', '1200','1600' UNION ALL
	SELECT 2000, 201, 'Wed', '1300','1700'
	;
	
	/* Insert your query below */


	/* Insert your query above */
	
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/
	DECLARE @x TABLE(
		AutoID INT IDENTITY,
		SchID INT,
		Patient INT,
		Nurse INT,
		dy CHAR(3),
		St CHAR(4),
		En CHAR(4),
		Overlapping CHAR(3)
	)

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

	DECLARE @z TABLE(
		AutoID INT IDENTITY,
		SchID INT,
		Patient INT,
		Nurse INT,
		dy CHAR(3),
		St CHAR(4),
		En CHAR(4),
		Overlapping CHAR(3)
	)

INSERT INTO @z(SchID, Patient, Nurse, dy, St, En, Overlapping)
	SELECT 1, 2000, 201, 'Mon', '0800', '1200', 'No' 
    UNION ALL
	SELECT 2, 2000, 201, 'Mon', '0900', '1300', 'Yes' 
    UNION ALL
	SELECT 3, 2000, 201, 'Mon', '1000', '1400', 'Yes' 
    UNION ALL
	SELECT 4, 2000, 201, 'Mon', '1100', '1600', 'Yes' 
    UNION ALL
	SELECT 5, 2000, 201, 'Mon', '1500', '1700', 'No' 
    UNION ALL				
	SELECT 6, 2000, 201, 'Tue', '0800', '1000', 'No' 
    UNION ALL
	SELECT 7, 2000, 201, 'Tue', '0900', '1200', 'Yes'
    UNION ALL
	SELECT 8, 2000, 201, 'Tue', '1100', '1400', 'No' 
    UNION ALL
	SELECT 9, 2000, 201, 'Tue', '1300', '1600', 'Yes' 
    UNION ALL
	SELECT 10,2000, 201, 'Tue', '1500', '1700', 'No' 
    UNION ALL
	SELECT 11, 2000, 201, 'Wed', '0800', '1000', 'No' 
    UNION ALL
	SELECT 12, 2000, 201, 'Wed', '0900', '1400', 'Yes' 
    UNION ALL
	SELECT 13, 2000, 201, 'Wed', '1100', '1500', 'No' 
    UNION ALL
	SELECT 14, 2000, 201, 'Wed', '1200', '1600', 'Yes' 
    UNION ALL
	SELECT 15, 2000, 201, 'Wed', '1300', '1700', 'Yes' 

-- 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