Database Challenges

--Muhammad_AlPasha_tsqlchallenge_19_V8.sql
--Muhammad_AlPasha_tsqlchallenge_19_V8.sql
WITH NumbersCTE AS
(
  SELECT 1 AS n 
  
  UNION ALL 
  
  SELECT 2
)
, SortPathAndLevelCTE AS
(
  SELECT T.EmployeeID, 0 AS level, N.n, CAST(ROW_NUMBER() OVER(ORDER BY LastName, FirstName) AS VARBINARY(MAX)) AS sort_path
    FROM @emp AS T
         CROSS JOIN
         NumbersCTE AS N
  WHERE T.ReportsTo IS NULL

  UNION ALL
  
  SELECT Curr.EmployeeID, Prev.level + 1, N.n,
         Prev.sort_path + CAST(ROW_NUMBER() OVER(PARTITION BY Curr.ReportsTo ORDER BY Curr.LastName, Curr.FirstName, N.n) AS BINARY(4))
    FROM SortPathAndLevelCTE AS Prev
         INNER JOIN
         @emp AS Curr
         ON Prev.n = 1
            AND Curr.ReportsTo = Prev.EmployeeID
         CROSS JOIN
         NumbersCTE AS N
)
, SortValueCTE AS
(
  SELECT EmployeeID, level, ROW_NUMBER() OVER(ORDER BY sort_path) AS sort_value
    FROM SortPathAndLevelCTE
)
, NestedSetsCTE AS
(
  SELECT EmployeeID, level, MIN(sort_value) AS lft, MAX(sort_value) AS rgt
    FROM SortValueCTE
   GROUP BY EmployeeID, level
)
, OrdersCountCTE AS
(
   SELECT T1.EmployeeID, T1.level, T1.lft, T1.rgt, COUNT(T2.OrderID) AS orders_count
     FROM NestedSetsCTE AS T1
          LEFT OUTER JOIN
          @ord AS T2
          ON T1.EmployeeID = T2.EmployeeID
    GROUP BY T1.EmployeeID, T1.level, T1.lft, T1.rgt
)
, TotalsCTE AS
(
   SELECT T1.EmployeeID, T1.level, T1.lft, T1.orders_count AS by_self, SUM(COALESCE(T2.orders_count, 0)) AS by_sub
     FROM OrdersCountCTE AS T1
          LEFT OUTER JOIN
          OrdersCountCTE AS T2
          ON T1.lft < T2.lft
             AND T1.rgt > T2.rgt
    GROUP BY T1.EmployeeID, T1.level, T1.lft, T1.orders_count
)
SELECT REPLICATE('    ', T1.level) + T2.LastName + ', ' + T2.FirstName AS Name, T1.level AS Level,
       COALESCE(T1.by_self, 0) AS by_self, T1.by_sub, COALESCE(T1.by_self, 0) + T1.by_sub AS total
  FROM TotalsCTE AS T1
       INNER JOIN
       @emp AS T2
       ON T1.EmployeeID = T2.EmployeeID
 ORDER BY T1.lft
OPTION(MAXRECURSION 0,MAXDOP 1)

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.