Database Challenges

--Jesse_Roberge_tsqlchallenge_19_V2.sql
WITH
	Hierarchy AS (
			SELECT
				EmployeeID, ReportsTo, FirstName, LastName
				,0 AS Level
				,CONVERT(VarChar(1000), CONVERT(VarChar(10),EmployeeID) + '|') AS Lineage
				,CONVERT(VarChar(1000), LastName+','+FirstName+','+CONVERT(VarChar(10),EmployeeID) + '|') AS LineageSort
			FROM @Emp AS Emp
			WHERE ReportsTo IS NULL
		UNION ALL
			SELECT
				Emp.EmployeeID, Emp.ReportsTo, Emp.FirstName, Emp.LastName
				,Hierarchy.Level+1 AS Level
				,CONVERT(VarChar(1000), Hierarchy.Lineage + CONVERT(VarChar(10),Emp.EmployeeID) + '|') AS Lineage
				,CONVERT(VarChar(1000), Hierarchy.LineageSort + Emp.LastName+','+Emp.FirstName+','+CONVERT(VarChar(10),Emp.EmployeeID) + '|') AS LineageSort
			FROM Hierarchy JOIN @Emp AS Emp ON Hierarchy.EmployeeID=Emp.ReportsTo
	)
	,Orders AS (
		SELECT EmployeeID, COUNT(*) AS OrderCount FROM @ord AS Ord GROUP BY EmployeeID
	)
SELECT
	REPLICATE(' ', 4*MAX(Hierarchy.Level)) + MAX(Hierarchy.LastName) + ', ' + MAX(Hierarchy.FirstName) AS Name
	,MAX(Hierarchy.Level) AS Level
	,MAX(ISNULL(Ord.OrderCount,0)) AS By_Self, SUM(ISNULL(OrdSub.OrderCount,0)) AS By_Sub
	,MAX(ISNULL(Ord.OrderCount,0)) + SUM(ISNULL(OrdSub.OrderCount,0)) AS Total
	--,Lineage, LineageSort
FROM
	Hierarchy
	LEFT OUTER JOIN Orders AS Ord ON Hierarchy.EmployeeID=Ord.EmployeeID
	LEFT OUTER JOIN Hierarchy AS HierarchySub ON HierarchySub.Lineage LIKE Hierarchy.Lineage + '%' AND Hierarchy.EmployeeID<>HierarchySub.EmployeeID
	LEFT OUTER JOIN Orders AS OrdSub ON HierarchySub.EmployeeID=OrdSub.EmployeeID
GROUP BY Hierarchy.EmployeeID
ORDER BY MAX(Hierarchy.LineageSort)

/*
SET STATISTICS TIME ON
SET STATISTICS IO ON

DECLARE @emp TABLE (
	EmployeeID INT,
	FirstName VARCHAR(15),
	LastName VARCHAR(15),
	ReportsTo INT
)

DECLARE @ord TABLE (
	OrderID INT,
	EmployeeID INT
)

INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo)
SELECT 2,'Andrew','Fuller',NULL UNION ALL
SELECT 1,'Nancy','Davolio',2 UNION ALL
SELECT 3,'Janet','Leverling',2 UNION ALL
SELECT 4,'Margaret','Peacock',2 UNION ALL
SELECT 5,'Steven','Buchanan',2 UNION ALL
SELECT 8,'Laura','Callahan',2 UNION ALL
SELECT 6,'Michael','Suyama',5 UNION ALL
SELECT 7,'Robert','King',5 UNION ALL
SELECT 9,'Anne','Dodsworth',5

INSERT INTO @ord (OrderID, EmployeeID)
SELECT 10258,1 UNION ALL
SELECT 10270,1 UNION ALL
SELECT 10275,1 UNION ALL
SELECT 10265,2 UNION ALL
SELECT 10277,2 UNION ALL
SELECT 10251,3 UNION ALL
SELECT 10253,3 UNION ALL
SELECT 10256,3 UNION ALL
SELECT 10250,4 UNION ALL
SELECT 10252,4 UNION ALL
SELECT 10248,5 UNION ALL
SELECT 10254,5 UNION ALL
SELECT 10249,6 UNION ALL
SELECT 10289,7 UNION ALL
SELECT 10303,7 UNION ALL
SELECT 10308,7 UNION ALL
SELECT 10262,8 UNION ALL
SELECT 10268,8 UNION ALL
SELECT 10276,8 UNION ALL
SELECT 10278,8 UNION ALL
SELECT 10255,9 UNION ALL
SELECT 10263,9;

--SELECT * FROM @emp
--SELECT * FROM @ord;
*/

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.