Database Challenges

--mark_cowne_tsqlchallenge_19.sql

WITH EmpOrd(EmployeeID,Name,ReportsTo,by_self) AS (
SELECT e.EmployeeID,
       e.LastName+', '+e.FirstName,
       e.ReportsTo,
       COUNT(o.OrderID)
FROM @emp e      
LEFT OUTER JOIN @ord o ON o.EmployeeID=e.EmployeeID
GROUP BY e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo),
Recur(EmployeeID,Name,StartPos,Level,by_self,FullPath) AS (
SELECT EmployeeID,
       Name,
       0,
       0,
       by_self,
       CAST(Name AS VARCHAR(8000))
FROM EmpOrd
WHERE ReportsTo IS NULL

UNION ALL

SELECT e.EmployeeID,
       e.Name,
       CAST(LEN(r.FullPath) AS INT),
       r.Level+1,
       e.by_self,
       r.FullPath + CAST(e.Name AS VARCHAR(8000))
FROM EmpOrd e
INNER JOIN Recur r ON r.EmployeeID=e.ReportsTo)
SELECT SPACE(r1.Level*4)+r1.Name AS Name,
       r1.Level,
       r1.by_self,
       SUM(r2.by_self)-r1.by_self AS by_sub,
       SUM(r2.by_self) AS total
FROM Recur r1
INNER JOIN Recur r2 ON SUBSTRING(r2.FullPath,r1.StartPos+1,LEN(r1.Name))=r1.Name
GROUP BY r1.Name,r1.Level,r1.by_self,r1.FullPath
ORDER BY r1.FullPath

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.