Database Challenges

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

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

 2. Insert your query (solution for TSQL Challenge 19) 
    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. 

 4. Tricky Cases
	- Five hierarchy levels.
	- Some top-level employees, with or without subordinates.
	- Some orders with non-existent employeeid or null.
	- Two employees with the same firstname and lastname.
	- Hierarchy loop: 5 reports to 7, 7 reports to 17, 17 reports to 5.
	  These must not be included because they do not belong to any top-level employe.
	
 Revision History:
 Rev 00 - 14 April 2010 - Tomas Boixet	- Initial Release
 Rev 01 - 16 April 2010 - Tomas Boixet	- Fixed some issues:
	+ Removed NULLs / zero length data on FirstName and LastName
	+ ReportsTo is always an existing EmployeeId, other than itself
	+ OrderId is now unique and not null
***********************************************************************/
/*
	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

	DECLARE @emp TABLE(
		EmployeeID int NOT NULL,
		FirstName varchar(30) NOT NULL,
		LastName varchar(30) NOT NULL,
		ReportsTo int NULL
	);

	DECLARE @ord TABLE(
		OrderID INT NOT NULL,
		EmployeeID INT NULL
	);

	INSERT INTO @emp (EmployeeID, FirstName, LastName, ReportsTo)
	SELECT 1, 'Nancy', 'Davolio', 24 UNION ALL
	SELECT 2, 'Andrew', 'Fuller', 23 UNION ALL
	SELECT 3, 'Janet', 'Leverling', 24 UNION ALL
	SELECT 4, 'Margaret', 'Peacock', NULL UNION ALL
	SELECT 5, 'Steven', 'Buchanan', 7 UNION ALL
	SELECT 6, 'Michael', 'Suyama', 39 UNION ALL
	SELECT 7, 'Robert', 'King', 17 UNION ALL
	SELECT 8, 'Laura', 'Callahan', 1 UNION ALL
	SELECT 9, 'Anne', 'Dodsworth', 43 UNION ALL
	SELECT 10, 'Palle', 'Ibsen', 36 UNION ALL
	SELECT 11, 'Karin', 'Josephs', 39 UNION ALL
	SELECT 12, 'Matti', 'Karttunen', 2 UNION ALL
	SELECT 13, 'Laurence', 'Lebihan', 38 UNION ALL
	SELECT 14, 'Timothy', 'O''Rourke', 38 UNION ALL
	SELECT 15, 'Roland', 'Mendel', 8 UNION ALL
	SELECT 16, 'Mary', 'Saveley', 36 UNION ALL
	SELECT 17, 'Howard', 'Snyder', 5 UNION ALL
	SELECT 18, 'Martin', 'Sommer', 49 UNION ALL
	SELECT 19, 'Gary', 'Thomas', 29 UNION ALL
	SELECT 20, 'Daniel', 'Tonini', 35 UNION ALL
	SELECT 21, 'Martine', 'Rance', 45 UNION ALL
	SELECT 22, 'Elizabeth', 'Lincoln', 12 UNION ALL
	SELECT 23, 'Anabela', 'Domingues', 38 UNION ALL
	SELECT 24, 'Peter', 'Franken', NULL UNION ALL
	SELECT 25, 'Paul', 'Henriot', 37 UNION ALL
	SELECT 26, 'Victoria', 'Ashworth', NULL UNION ALL
	SELECT 27, 'Helen', 'Bennett', 15 UNION ALL
	SELECT 28, 'Lesley', 'Brown Smith', 31 UNION ALL
	SELECT 29, 'Paolo', 'Accorti', 38 UNION ALL
	SELECT 30, 'Pedro', 'Afonso', 12 UNION ALL
	SELECT 31, 'Philip', 'Smith', 38 UNION ALL
	SELECT 32, 'Maria', 'Larsson', 40 UNION ALL
	SELECT 33, 'Yoshi', 'Latimer', 37 UNION ALL
	SELECT 34, 'Sven', 'Ottlieb', 28 UNION ALL
	SELECT 35, 'Miguel', 'Paolino', 47 UNION ALL
	SELECT 36, 'Philip', 'Smith', 48 UNION ALL
	SELECT 37, 'Diego', 'Roel', 43 UNION ALL
	SELECT 38, 'Carine', 'Schmitt', NULL UNION ALL
	SELECT 39, 'Margaret', 'Smith', 38 UNION ALL
	SELECT 40, 'Rita', 'Muller', 31 UNION ALL
	SELECT 41, 'Maria', 'Pontes', 38 UNION ALL
	SELECT 42, 'Janine', 'Labrune', 34 UNION ALL
	SELECT 43, 'Ann', 'Devon', 47 UNION ALL
	SELECT 44, 'Francisco', 'Chang', 3 UNION ALL
	SELECT 45, 'Aria', 'Cruz', 48 UNION ALL
	SELECT 46, 'Carlos J.', 'Hernadez', 44 UNION ALL
	SELECT 47, 'Pirkko', 'Koskitalo', 24 UNION ALL
	SELECT 48, 'Karla', 'Jablonski', NULL UNION ALL
	SELECT 49, 'Helvetius', 'Nagy', 21 UNION ALL
	SELECT 50, 'Annette', 'Roulet', 51 UNION ALL
	SELECT 51, 'Manuel', 'Pereira', 31 UNION ALL
	SELECT 52, 'Patricia', 'McKenna', 48 ;

	INSERT INTO @ord (OrderID, EmployeeID)
	SELECT 10240, 22 UNION ALL
	SELECT 10241, 53 UNION ALL
	SELECT 10242, 3 UNION ALL
	SELECT 10243, 46 UNION ALL
	SELECT 10244, 1 UNION ALL
	SELECT 10245, 3 UNION ALL
	SELECT 10246, 19 UNION ALL
	SELECT 10247, 20 UNION ALL
	SELECT 10248, 45 UNION ALL
	SELECT 10249, 6 UNION ALL
	SELECT 10252, 1 UNION ALL
	SELECT 10253, 52 UNION ALL
	SELECT 10254, 51 UNION ALL
	SELECT 10255, 33 UNION ALL
	SELECT 10256, 123456789 UNION ALL
	SELECT 10258, NULL UNION ALL
	SELECT 10259, 16 UNION ALL
	SELECT 10260, -50 UNION ALL
	SELECT 10261, 33 UNION ALL
	SELECT 10262, 21 UNION ALL
	SELECT 10263, 48 UNION ALL
	SELECT 10264, 35 UNION ALL
	SELECT 10265, 40 UNION ALL
	SELECT 10266, 52 UNION ALL
	SELECT 10268, 45 UNION ALL
	SELECT 10269, 3 UNION ALL
	SELECT 10270, 28 UNION ALL
	SELECT 10271, 35 UNION ALL
	SELECT 10272, 49 UNION ALL
	SELECT 10273, 8 UNION ALL
	SELECT 10274, 8 UNION ALL
	SELECT 10276, 10 UNION ALL
	SELECT 10277, 52 UNION ALL
	SELECT 10278, 6 UNION ALL
	SELECT 10279, 40 UNION ALL
	SELECT 10281, 4 UNION ALL
	SELECT 10282, 33 UNION ALL
	SELECT 10283, 52 UNION ALL
	SELECT 10284, 25 UNION ALL
	SELECT 10285, 41 UNION ALL
	SELECT 10286, 36 UNION ALL
	SELECT 10287, 40 UNION ALL
	SELECT 10288, 5 UNION ALL
	SELECT 10289, 16 UNION ALL
	SELECT 10290, 29 UNION ALL
	SELECT 10291, 40 UNION ALL
	SELECT 10292, 39 UNION ALL
	SELECT 10293, 36 UNION ALL
	SELECT 10294, 3 UNION ALL
	SELECT 10295, 41 UNION ALL
	SELECT 10296, 1 UNION ALL
	SELECT 10297, 15 UNION ALL
	SELECT 10298, 4 UNION ALL
	SELECT 10299, 17 UNION ALL
	SELECT 10300, 46 UNION ALL
	SELECT 10301, 28 UNION ALL
	SELECT 10302, 16 UNION ALL
	SELECT 10304, 25 UNION ALL
	SELECT 10305, 34 UNION ALL
	SELECT 10306, NULL UNION ALL
	SELECT 10307, 46 UNION ALL
	SELECT 10308, 41 UNION ALL
	SELECT 10309, 34 UNION ALL
	SELECT 10310, 6 UNION ALL
	SELECT 10311, 6 UNION ALL
	SELECT 10312, 16 UNION ALL
	SELECT 10313, 16 UNION ALL
	SELECT 10314, 6 UNION ALL
	SELECT 10315, 30 UNION ALL
	SELECT 10316, 48 UNION ALL
	SELECT 10317, 17 UNION ALL
	SELECT 10318, 49 UNION ALL
	SELECT 10319, 47 UNION ALL
	SELECT 10320, 3 UNION ALL
	SELECT 10321, 46 UNION ALL
	SELECT 10322, 48 UNION ALL
	SELECT 10323, 15 UNION ALL
	SELECT 10324, 11 UNION ALL
	SELECT 10325, 46 UNION ALL
	SELECT 10326, 38 UNION ALL
	SELECT 10327, 7 UNION ALL
	SELECT 10328, 44 UNION ALL
	SELECT 10329, 19 UNION ALL
	SELECT 10331, 22 UNION ALL
	SELECT 10332, 33 UNION ALL
	SELECT 10333, 48 UNION ALL
	SELECT 10336, 36 UNION ALL
	SELECT 10338, 39 UNION ALL
	SELECT 10342, 20 UNION ALL
	SELECT 10343, 36 UNION ALL
	SELECT 10345, 35 UNION ALL
	SELECT 10346, 14 UNION ALL
	SELECT 10347, 0 UNION ALL
	SELECT 10348, 11 UNION ALL
	SELECT 10349, 20 UNION ALL
	SELECT 10350, 17 UNION ALL
	SELECT 10351, 27 UNION ALL
	SELECT 10352, 1 UNION ALL
	SELECT 10353, 5 UNION ALL
	SELECT 10354, 41 UNION ALL
	SELECT 10356, 11 UNION ALL
	SELECT 10357, 41 UNION ALL
	SELECT 10358, 45 UNION ALL
	SELECT 10359, 48 UNION ALL
	SELECT 10360, 38 UNION ALL
	SELECT 10361, 17 UNION ALL
	SELECT 10363, 17 UNION ALL
	SELECT 10364, 45 UNION ALL
	SELECT 10366, 32 UNION ALL
	SELECT 10367, 27 UNION ALL
	SELECT 10368, 21 UNION ALL
	SELECT 10369, NULL UNION ALL
	SELECT 10370, 36 UNION ALL
	SELECT 10371, 19 UNION ALL
	SELECT 10372, 1 UNION ALL
	SELECT 10373, 42 UNION ALL
	SELECT 10374, 37 UNION ALL
	SELECT 10375, 5 UNION ALL
	SELECT 10377, 42 UNION ALL
	SELECT 10378, 12 UNION ALL
	SELECT 10379, 4 UNION ALL
	SELECT 10380, 22 UNION ALL
	SELECT 10381, 19 UNION ALL
	SELECT 10382, -1 UNION ALL
	SELECT 10384, 28 UNION ALL
	SELECT 10385, 4 UNION ALL
	SELECT 10386, 52 UNION ALL
	SELECT 10387, 21 UNION ALL
	SELECT 10388, 21 UNION ALL
	SELECT 10390, 6 UNION ALL
	SELECT 10391, 29 UNION ALL
	SELECT 10392, 11 UNION ALL
	SELECT 10395, 47 UNION ALL
	SELECT 10396, 21 UNION ALL
	SELECT 10397, 44 UNION ALL
	SELECT 10398, 45 UNION ALL
	SELECT 10400, 22 UNION ALL
	SELECT 10401, 46 UNION ALL
	SELECT 10404, 1 UNION ALL
	SELECT 10405, 10 UNION ALL
	SELECT 10407, 37 UNION ALL
	SELECT 10408, 11 UNION ALL
	SELECT 10409, 41 UNION ALL
	SELECT 10411, 47 UNION ALL
	SELECT 10413, 52 UNION ALL
	SELECT 10414, 5 UNION ALL
	SELECT 10415, 41 UNION ALL
	SELECT 123456789, 33 ;

	/* Insert your query below */



	/* Insert your query above */

END
GO

SET NOCOUNT ON

/*
	Start the testing phase
*/

DECLARE @x TABLE(
	AutoID INT IDENTITY,
	[Name] VARCHAR(2000),
	[Level] INT,
	by_self INT,
	by_sub INT,
	total INT
)

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

DECLARE @z TABLE(
	AutoID INT IDENTITY,
	[Name] VARCHAR(2000),
	[Level] INT,
	by_self INT,
	by_sub INT,
	total INT
)

INSERT INTO @z([Name],[Level],by_self,by_sub,total) 
SELECT 'Ashworth, Victoria',0,0,0,0 UNION ALL 
SELECT 'Franken, Peter',0,0,43,43 UNION ALL 
SELECT '    Davolio, Nancy',1,6,6,12 UNION ALL 
SELECT '        Callahan, Laura',2,2,4,6 UNION ALL 
SELECT '            Mendel, Roland',3,2,2,4 UNION ALL 
SELECT '                Bennett, Helen',4,2,0,2 UNION ALL 
SELECT '    Koskitalo, Pirkko',1,3,15,18 UNION ALL 
SELECT '        Devon, Ann',2,0,9,9 UNION ALL 
SELECT '            Dodsworth, Anne',3,0,0,0 UNION ALL 
SELECT '            Roel, Diego',3,2,7,9 UNION ALL 
SELECT '                Henriot, Paul',4,2,0,2 UNION ALL 
SELECT '                Latimer, Yoshi',4,5,0,5 UNION ALL 
SELECT '        Paolino, Miguel',2,3,3,6 UNION ALL 
SELECT '            Tonini, Daniel',3,3,0,3 UNION ALL 
SELECT '    Leverling, Janet',1,5,8,13 UNION ALL 
SELECT '        Chang, Francisco',2,2,6,8 UNION ALL 
SELECT '            Hernadez, Carlos J.',3,6,0,6 UNION ALL 
SELECT 'Jablonski, Karla',0,5,30,35 UNION ALL 
SELECT '    Cruz, Aria',1,5,7,12 UNION ALL 
SELECT '        Rance, Martine',2,5,2,7 UNION ALL 
SELECT '            Nagy, Helvetius',3,2,0,2 UNION ALL 
SELECT '                Sommer, Martin',4,0,0,0 UNION ALL 
SELECT '    McKenna, Patricia',1,6,0,6 UNION ALL 
SELECT '    Smith, Philip',1,5,7,12 UNION ALL 
SELECT '        Ibsen, Palle',2,2,0,2 UNION ALL 
SELECT '        Saveley, Mary',2,5,0,5 UNION ALL 
SELECT 'Peacock, Margaret',0,4,0,4 UNION ALL 
SELECT 'Schmitt, Carine',0,2,46,48 UNION ALL 
SELECT '    Accorti, Paolo',1,2,4,6 UNION ALL 
SELECT '        Thomas, Gary',2,4,0,4 UNION ALL 
SELECT '    Domingues, Anabela',1,0,6,6 UNION ALL 
SELECT '        Fuller, Andrew',2,0,6,6 UNION ALL 
SELECT '            Karttunen, Matti',3,1,5,6 UNION ALL 
SELECT '                Afonso, Pedro',4,1,0,1 UNION ALL 
SELECT '                Lincoln, Elizabeth',4,4,0,4 UNION ALL 
SELECT '    Lebihan, Laurence',1,0,0,0 UNION ALL 
SELECT '    O''Rourke, Timothy',1,1,0,1 UNION ALL 
SELECT '    Pontes, Maria',1,7,0,7 UNION ALL 
SELECT '    Smith, Margaret',1,2,11,13 UNION ALL 
SELECT '        Josephs, Karin',2,5,0,5 UNION ALL 
SELECT '        Suyama, Michael',2,6,0,6 UNION ALL 
SELECT '    Smith, Philip',1,0,13,13 UNION ALL 
SELECT '        Brown Smith, Lesley',2,3,4,7 UNION ALL 
SELECT '            Ottlieb, Sven',3,2,2,4 UNION ALL 
SELECT '                Labrune, Janine',4,2,0,2 UNION ALL 
SELECT '        Muller, Rita',2,4,1,5 UNION ALL 
SELECT '            Larsson, Maria',3,1,0,1 UNION ALL 
SELECT '        Pereira, Manuel',2,1,0,1 UNION ALL 
SELECT '            Roulet, Annette',3,0,0,0;

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