Database Challenges

--eric_nordlander_tsqlchallenge_19.sql
;WITH EmpsnOrdsCTE AS
(
	SELECT	E.EmployeeID, Name = E.LastName + ', ' + E.FirstName, E.ReportsTo,
		SelfOrders = SUM(CASE WHEN O.OrderID IS NOT NULL THEN 1 ELSE 0 END)
	 FROM	@emp E
		LEFT  JOIN @ord O ON E.EmployeeID = O.EmployeeID
	 GROUP	BY E.EmployeeID, E.FirstName, E.LastName, E.ReportsTo
)
,EmpCTE AS
(
	SELECT	EmployeeID, Name, ReportsTo, [Level] = 0, SelfOrders,
		OrderBy = CONVERT(VARCHAR(MAX), REPLICATE('0', 7 - LEN(CONVERT(VARCHAR(7), ROW_NUMBER() OVER (ORDER BY Name)))) + CONVERT(VARCHAR(7), ROW_NUMBER() OVER (ORDER BY Name)))
	 FROM	EmpsnOrdsCTE A
	 WHERE	ReportsTo IS NULL
	UNION ALL
	SELECT	B.EmployeeID, B.Name, B.ReportsTo, [Level] = A.[Level] + 1, B.SelfOrders,
		OrderBy = CONVERT(VARCHAR(MAX), A.OrderBy + '.' + REPLICATE('0', 7 - LEN(CONVERT(VARCHAR(7), ROW_NUMBER() OVER (ORDER BY B.Name)))) + CONVERT(VARCHAR(7), ROW_NUMBER() OVER (ORDER BY B.Name)))
	 FROM	EmpCTE A
		INNER JOIN EmpsnOrdsCTE B ON A.EmployeeID = B.ReportsTo
)
,OrdCTE AS
(
	SELECT	ReportsTo, SubOrders = SUM(SelfOrders)
	 FROM	EmpCTE
	 WHERE	ReportsTo IS NOT NULL
	 GROUP	BY ReportsTo
	UNION ALL
	SELECT	E.ReportsTo, A.SubOrders
	 FROM	OrdCTE A
		INNER JOIN EmpCTE E ON A.ReportsTo = E.EmployeeID
	 WHERE	E.ReportsTo IS NOT NULL
)

SELECT	Name = CONVERT(VARCHAR(50), REPLICATE(' ', E.[Level] * 4) + E.Name),
	E.[Level],
	by_self = E.SelfOrders, 
	by_sub = SUM(ISNULL(O.SubOrders, 0)),
	total = E.SelfOrders + SUM(ISNULL(O.SubOrders, 0))
 FROM	EmpCTE E
	LEFT  JOIN OrdCTE O ON E.EmployeeID = O.ReportsTo
 GROUP	BY E.OrderBy,
	REPLICATE(' ', E.[Level] * 4) + E.Name,
	E.[Level],
	E.SelfOrders
 ORDER	BY E.OrderBy

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.