Database Challenges

Here is the data used for the basic testing of TSQL Challenge 19

DECLARE @emp TABLE (
	EmployeeID INT,
	FirstName VARCHAR(15),
	LastName VARCHAR(15),
	ReportsTo INT
)

DECLARE @ord TABLE (
	OrderID INT,
	EmployeeID INT
)

INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo)
SELECT 2,'Andrew','Fuller',NULL UNION ALL
SELECT 1,'Nancy','Davolio',2 UNION ALL
SELECT 3,'Janet','Leverling',2 UNION ALL
SELECT 4,'Margaret','Peacock',2 UNION ALL
SELECT 5,'Steven','Buchanan',2 UNION ALL
SELECT 8,'Laura','Callahan',2 UNION ALL
SELECT 6,'Michael','Suyama',5 UNION ALL
SELECT 7,'Robert','King',5 UNION ALL
SELECT 9,'Anne','Dodsworth',5
SELECT * FROM @emp

INSERT INTO @ord (OrderID, EmployeeID) 
SELECT 10258,1 UNION ALL
SELECT 10270,1 UNION ALL
SELECT 10275,1 UNION ALL
SELECT 10265,2 UNION ALL
SELECT 10277,2 UNION ALL
SELECT 10251,3 UNION ALL
SELECT 10253,3 UNION ALL
SELECT 10256,3 UNION ALL
SELECT 10250,4 UNION ALL
SELECT 10252,4 UNION ALL
SELECT 10248,5 UNION ALL
SELECT 10254,5 UNION ALL
SELECT 10249,6 UNION ALL
SELECT 10289,7 UNION ALL
SELECT 10303,7 UNION ALL
SELECT 10308,7 UNION ALL
SELECT 10262,8 UNION ALL
SELECT 10268,8 UNION ALL
SELECT 10276,8 UNION ALL
SELECT 10278,8 UNION ALL
SELECT 10255,9 UNION ALL
SELECT 10263,9 

SELECT * FROM @ord

Here is the expected results based on the above data

Name                      Level by_self by_sub total
------------------------- ----- ------- ------ -----
Fuller, Andrew            0     2       20     22
    Buchanan, Steven      1     2       6      8
        Dodsworth, Anne   2     2       0      2
        King, Robert      2     3       0      3
        Suyama, Michael   2     1       0      1
    Callahan, Laura       1     4       0      4
    Davolio, Nancy        1     3       0      3
    Leverling, Janet      1     3       0      3
    Peacock, Margaret     1     2       0      2