/*
 =============================================================================================
 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)