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

	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 
	;
  /*
 =============================================================================================
 CREATE DATE: 04/28/2010
  CREATED BY: SETH PHELABAUM
     PURPOSE: T-SQL Challenge 19 - Consolidated Calculations on Hierarchal Structures.
       NOTES:			
      ISSUES:		
 Revision History
 Date	 By		Change Made
 -------- ---  	-------------------------------------
 =============================================================================================
*/

;WITH -- Concatenate the names and Count the orders
	Comb AS (SELECT LastName + ', ' + E.FirstName Name, E.EmployeeID, E.ReportsTo, COUNT(O.OrderID) Orders
				FROM @emp E
					LEFT JOIN @ord O ON E.EmployeeID = O.EmployeeID
				GROUP BY E.EmployeeID, E.FirstName, E.LastName, E.ReportsTo),
	  -- Use a recursive CTE to generate the hierarchy and create the sort column
	Recurs AS (	SELECT Comb.* , 0 as EmpLevel, cast(CHAR(179) + comb.name + CHAR(179) as varchar(max)) Sort,
					CAST('/' + CAST(Comb.EmployeeID AS varchar(20)) + '/' AS varchar(max)) Inc
					FROM Comb
					WHERE ReportsTo IS NULL
					UNION ALL
					SELECT Comb.*, EmpLevel + 1, cast(Recurs.Sort + CHAR(179) + comb.name + CHAR(179) as varchar(max)) Sort,
					CAST(Recurs.Inc + '/' + CAST(Comb.EmployeeID AS varchar(20)) + '/' AS varchar(max))
					FROM Comb
						INNER JOIN Recurs ON Comb.ReportsTo = Recurs.EmployeeID
					)
SELECT REPLICATE(' ',EmpLevel*4) + Name Name, EmpLevel [Level], Orders by_self,
	ISNULL((SELECT SUM(Orders) FROM Recurs WHERE Inc LIKE '%/' + CAST(R.EmployeeID AS varchar(20)) +'//%'),0) by_sub,
	ISNULL((SELECT SUM(Orders) FROM Recurs WHERE Inc LIKE '%/' + CAST(R.EmployeeID AS varchar(20)) +'/%'),0) total
FROM Recurs R 
ORDER BY Sort OPTION (MAXRECURSION 32767)