Database Challenges

--morten_nilsen_tsqlchallenge_19.sql
;with employees (SortBy, Name, ReportsTo, EmployeeID, LastName, FirstName, Level)
as (
	select cast(LastName + '/' + FirstName as varchar(500)) as SortBy, LastName + ', ' + FirstName AS Name, ReportsTo, EmployeeID, LastName, FirstName, 0 as Level
	from @emp as emp
	where ReportsTo IS NULL
	union all
	select cast(employees.SortBy + '/' + emp.LastName + '/' + emp.FirstName  as varchar(500)) as SortBy, emp.LastName + ', ' + emp.FirstName AS Name, emp.ReportsTo, emp.EmployeeID, emp.LastName, emp.FirstName, employees.Level + 1 as Level
	from @emp as emp
	inner join employees on (emp.ReportsTo = employees.EmployeeID)
)
select replicate('    ',level) + LastName + ', ' + FirstName as Name, Level, by_self, by_sub, by_self + by_sub as total
from (
	select SortBy, LastName, FirstName, Level, 
		(select count(OrderID) from @ord where EmployeeID = employees.EmployeeID) as by_self
		,(select count(OrderID) from @ord as i where EmployeeID in (SELECT EmployeeID FROM employees as i2 WHERE SortBy like employees.SortBy + '/%')) as by_sub
	from employees
) as report
order by SortBy

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.