Database Challenges

You can use the TSQL template given below to test your solutions against the basic data of TSQL Challenge 31

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

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

 2. Insert your query (solution for TSQL Challenge 31) 
    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 - 21 March 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
	IF OBJECT_ID('TC31_Employee','U') is not null 
		DROP TABLE TC31_Employee
	;

	CREATE TABLE TC31_Employee(
	  empid INT,
	  empname VARCHAR(25))
	;

	IF OBJECT_ID('TC31_ResourceType','U') is not null 
		DROP TABLE TC31_ResourceType
	;

	CREATE TABLE TC31_ResourceType(
	  restypeid INT,
	  restypename VARCHAR(25))
	;

	IF OBJECT_ID('TC31_Department','U') is not null 
		DROP TABLE TC31_Department
	;

	CREATE TABLE TC31_Department(
	  deptid INT,
	  deptname VARCHAR(25))
	;

	IF OBJECT_ID('TC31_ResourceTypeAssoc','U') is not null 
		DROP TABLE TC31_ResourceTypeAssoc
	;

	CREATE TABLE TC31_ResourceTypeAssoc(
	  empid INT,
	  restypeid INT,
	  resfrom DATETIME,
	  resuntil DATETIME)
	;

	IF OBJECT_ID('TC31_DepartmentAssoc','U') is not null 
		DROP TABLE TC31_DepartmentAssoc
	;

	CREATE TABLE TC31_DepartmentAssoc(
	  empid INT,
	  deptid INT,
	  deptfrom DATETIME,
	  deptuntil DATETIME)
	;

	IF OBJECT_ID('TC31_Holiday','U') is not null 
		DROP TABLE TC31_Holiday
	;

	CREATE TABLE TC31_Holiday(
	  holdate DATETIME)
	;

	IF OBJECT_ID('TC31_Vacation','U') is not null 
		DROP TABLE TC31_Vacation
	;

	CREATE TABLE TC31_Vacation(
	  empid INT,
	  vacfrom DATETIME,
	  vacuntil DATETIME)
	;

	IF OBJECT_ID('TC31_EstimatedHours','U') is not null 
		DROP TABLE TC31_EstimatedHours
	;

	CREATE TABLE TC31_EstimatedHours(
	  empid INT,
	  workmonth DATETIME,
	  workhours DECIMAL(8,2))
	;

	INSERT INTO TC31_Employee VALUES(1,'John')
	INSERT INTO TC31_Employee VALUES(2,'Frank')
	;

	INSERT INTO TC31_Resourcetype VALUES(1,'Project Manager')
	INSERT INTO TC31_Resourcetype VALUES(2,'Sales Assistant')
	INSERT INTO TC31_Resourcetype VALUES(3,'Client Service Manager')
	INSERT INTO TC31_Resourcetype VALUES(4,'Functional Analyst')
	INSERT INTO TC31_Resourcetype VALUES(5,'Application Expert')
	INSERT INTO TC31_Resourcetype VALUES(6,'DBA')
	;

	INSERT INTO TC31_Department VALUES(1,'Department A')
	INSERT INTO TC31_Department VALUES(2,'Department B')
	INSERT INTO TC31_Department VALUES(3,'Department C')
	;

	INSERT INTO TC31_ResourceTypeAssoc VALUES(1,2,'2010-01-01','2010-03-15')
	INSERT INTO TC31_ResourceTypeAssoc VALUES(1,1,'2010-03-16','2010-04-15')
	INSERT INTO TC31_ResourceTypeAssoc VALUES(1,3,'2010-04-16',null)
	INSERT INTO TC31_ResourceTypeAssoc VALUES(2,4,'2010-01-01','2010-01-15')
	INSERT INTO TC31_ResourceTypeAssoc VALUES(2,5,'2010-01-16','2010-01-20')
	INSERT INTO TC31_ResourceTypeAssoc VALUES(2,6,'2010-01-21',null)
	;

	INSERT INTO TC31_DepartmentAssoc VALUES(1,1,'2010-01-01','2010-02-28')
	INSERT INTO TC31_DepartmentAssoc VALUES(1,3,'2010-03-01',null)
	INSERT INTO TC31_DepartmentAssoc VALUES(2,2,'2010-01-01','2010-02-28')
	INSERT INTO TC31_DepartmentAssoc VALUES(2,3,'2010-03-01',null)
	;

	INSERT INTO TC31_Holiday VALUES('2010-01-01')
	INSERT INTO TC31_Holiday VALUES('2010-01-06')
	INSERT INTO TC31_Holiday VALUES('2010-04-01')
	INSERT INTO TC31_Holiday VALUES('2010-04-05')
	;

	INSERT INTO TC31_Vacation VALUES(1,'2010-01-25','2010-02-05')
	INSERT INTO TC31_Vacation VALUES(2,'2010-01-20','2010-02-01') 
         ;

	INSERT INTO TC31_EstimatedHours VALUES(1,'2010-01-01',190.5)
	INSERT INTO TC31_EstimatedHours VALUES(1,'2010-02-01',175.25)
	INSERT INTO TC31_EstimatedHours VALUES(1,'2010-03-01',150)
	INSERT INTO TC31_EstimatedHours VALUES(1,'2010-04-01',180)
	INSERT INTO TC31_EstimatedHours VALUES(2,'2010-01-01',200)
	INSERT INTO TC31_EstimatedHours VALUES(2,'2010-02-01',190)
	INSERT INTO TC31_EstimatedHours VALUES(2,'2010-03-01',180)
	INSERT INTO TC31_EstimatedHours VALUES(2,'2010-04-01',170)
	; 

	/* Insert your query below */

	/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/

DECLARE @x TABLE(
	AutoID INT IDENTITY,
	Department VARCHAR(50),
	ResourceType VARCHAR(50),
	Month VARCHAR(50),
	Hours DECIMAL(8,2)
)

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

DECLARE @z TABLE (
	AutoID INT IDENTITY,
	Department VARCHAR(50),
	ResourceType VARCHAR(50),
	Month VARCHAR(50),
	Hours DECIMAL(8,2)
)

INSERT INTO @z(Department, ResourceType, Month, Hours)
SELECT 'Department A','Sales Assistant','2010-01',190.50
UNION ALL
SELECT 'Department A','Sales Assistant','2010-02',175.25
UNION ALL
SELECT 'Department B','Application Expert','2010-01',31.58
UNION ALL
SELECT 'Department B','DBA','2010-01',73.68
UNION ALL
SELECT 'Department B','DBA','2010-02',190.00
UNION ALL
SELECT 'Department B','Functional Analyst','2010-01',94.74
UNION ALL
SELECT 'Department C','Client Service Manager','2010-04',99.00
UNION ALL
SELECT 'Department C','DBA','2010-03',180.00
UNION ALL
SELECT 'Department C','DBA','2010-04',170.00
UNION ALL
SELECT 'Department C','Project Manager','2010-03',78.26
UNION ALL
SELECT 'Department C','Project Manager','2010-04',81.00
UNION ALL
SELECT 'Department C','Sales Assistant','2010-03',71.74

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