Database Challenges

--bret_warnick_tsqlchallenge_19.sql
--For the first CTE,
--In addition to the emp data already in the table, get the concatenated 
--lastname, firstname, the number of orders for the employee, and get a 
--uniqueid that is also ordered by lastname, firstname.  The id will be 
--turned into an equal length string (I picked 10 as the length since 
--that is as many digits as can be in an int).
--This id will be used for ordering (all ids of those lower in the 
--hierarchy will be appended as we create the hierachy to get the 
--correct ordering).  This string meant for ordering will also be 
--used to create the total orders as a given employee's unique id 
--assigned here will be in the string for themselves as well as anyone 
--below them.
;with EmpOrders 
	(EmployeeID
		, FirstName
		, LastName
		, ReportsTo
		, Name
		, by_self
		, OrderingAndID)
 as (select EmployeeID
		, FirstName
		, LastName
		, ReportsTo
		, LastName+', '+FirstName
		, (select COUNT(1) 
			from @ord 
			where EmployeeID=e.EmployeeID) as by_self
		, convert(varchar(max),right('0000000000'+CONVERT(varchar(10),ROW_NUMBER() over (order by lastname, firstname)),10)++'|')
		from @emp e)
--Create the hierarchy with a recursive CTE
,EmpHierarchy 
	(Name
		, Level
		, EmployeeID
		, by_self
		, ReportsTo
		, OrderingAndID)
 as (select Name
		, 0
		, EmployeeID
		, by_self
		, ReportsTo
		, OrderingAndID
	from EmpOrders e
	where ReportsTo is null
 union all
	select o.Name
		, h.Level+1
		, o.EmployeeID
		, o.by_self
		, o.ReportsTo
		, h.OrderingAndID+o.OrderingAndID
	from EmpOrders o inner join EmpHierarchy h on o.reportsto=h.employeeid )
--Finally select the data and prepend 4 spaces times the level
--also get the total by summing all rows where the uniqueid is found in the 
--OrderingAndID string (then the by_sub is just a simple calculation)
select Name
	, Level
	, by_self
	, total-by_self by_sub
	, total
	from
	(select space((Level)*4)+Name Name
		, Level
		, by_self
		, (select SUM(by_self) from EmpHierarchy where CHARINDEX(e.OrderingAndID,OrderingAndID)>0) total
		, OrderingAndID 
	  from EmpHierarchy e) s 
	order by OrderingAndID

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.