Database Challenges

--Gyan_Koju_tsqlchallenge_19.sql
;WITH cte (id,[Name], [level],lvl_order, rootID) AS (
	select EmployeeID
		,cast(LastName+', '+ FirstName as varchar(200))
		,0
		,cast('_'+cast(EmployeeID as varchar)+'_' as varchar(1000))
		, cast(right('000000000000'+ cast(ROW_NUMBER ( ) over(order by LastName,FirstName) as varchar),len(count( *) over (PARTITION BY ReportsTo))) as varchar(max)) as rootID
	from @emp where ReportsTo is null
	union all
	select EmployeeID
		,cast(replicate('    ',[level]+1)+LastName+', '+ FirstName as varchar(200))
		,[level]+1
		,cast(lvl_order+'_'+cast(EmployeeID as varchar)+'_' as varchar(1000)) 
		--formating number of digit for child row number by  length of total record within a parent,
		--so that it can be supported for maxium hierchy level and total number of records
		, cast(rootID+right('000000000000'+ cast(ROW_NUMBER ( ) over(order by LastName,FirstName) as varchar)
								,len(count(*) over (PARTITION BY ReportsTo))
							) as varchar(max)) as rootID
	from @emp e 
	inner join cte c on c.id=e.ReportsTo
)
select 
	max(case when e.id=e.EmployeeID then e.[name] else null end) [name]
	,min([level]) [level]
	,sum(case when e.id=e.EmployeeID then isnull(self_by,0) else 0 end) by_self
	,sum(case when e.id <>e.EmployeeID then isnull(self_by,0) else 0 end) by_sub
	,sum(isnull(self_by,0)) total 
	--,min(rootID)
from
(
	select c.id,e.EmployeeID,[name],lvl_order,[level],rootID
	from cte c inner join @emp e on charindex('_'+cast(e.EmployeeID as varchar)+'_', c.lvl_order) <>0 
) e
left join 
( 
	select EmployeeID,count(*) self_by from @ord  group by EmployeeID 
) o
on e.id=o.EmployeeID
group by e.EmployeeID
order by min(rootID)

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.