Database Challenges

--parth_patel_tsqlchallenge_19.sql
;WITH tmpEmployees (EmployeeID, LastName, FirstName, [Level], SortOrder, FullPath, Cycle) AS
(
	SELECT EmployeeID, LastName, FirstName, 0, 
	CAST(ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS VARBINARY(MAX)),
	CAST('.' + CAST(EmployeeID AS VARCHAR(10)) + '.' AS VARCHAR(MAX)), -- Generate full path from root
	0 -- Remove cyclic parent problem
	FROM @emp WHERE ReportsTo IS NULL
	
	UNION ALL
	
	SELECT E.EmployeeID, E.LastName, E.FirstName, T.[Level] + 1, 
	T.SortOrder + CAST(ROW_NUMBER() OVER (PARTITION BY E.ReportsTo ORDER BY E.LastName, E.FirstName) AS VARBINARY(4)),
	CAST(T.FullPath + CAST(E.EmployeeID AS VARCHAR(10)) + '.' AS VARCHAR(MAX)),
	CASE WHEN T.FullPath LIKE '%.' + CAST(E.EmployeeID AS VARCHAR(10)) + '.%' THEN 1 ELSE 0 END
	FROM @emp E INNER JOIN tmpEmployees T ON E.ReportsTo = T.EmployeeID AND T.Cycle = 0
),
tmpEmployeeOrders AS
(
	SELECT EmpID = EmployeeID, by_self = COUNT(OrderID) FROM @ord
	GROUP BY EmployeeID
),
tmpEmployeeSubOrders AS
(
	SELECT SubEmpID = EmployeeID, by_sub = COUNT(OrderID) 
	FROM tmpEmployees
	INNER JOIN 
	(
		SELECT OrderEmpID = O.EmployeeID, O.OrderID, OrderEmpFullPath = E.FullPath
		FROM @ord O INNER JOIN tmpEmployees E ON O.EmployeeID = E.EmployeeID
	) as Orders
	ON OrderEmpID <> EmployeeID AND CHARINDEX('.' + CAST(EmployeeID AS VARCHAR(10)) + '.', OrderEmpFullPath) > 0
	GROUP BY EmployeeID
)
SELECT 
Name = SPACE([Level]*4) + LastName + ', ' + FirstName,
[Level], by_self = ISNULL(by_self,0), by_sub = ISNULL(by_sub,0), total = ISNULL(by_self,0) + ISNULL(by_sub,0)
FROM tmpEmployees 
LEFT OUTER JOIN tmpEmployeeOrders ON EmployeeID = EmpID
LEFT OUTER JOIN tmpEmployeeSubOrders ON EmployeeID = SubEmpID
ORDER BY SortOrder
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.