Database Challenges

--Randy_Derby_TSQLchallenge_19.sql
with hier as
(select e.EmployeeID, FirstName, LastName, ReportsTo, 0 as lvl, cast(lastname+','+firstname as varchar(1000)) as name_path,
 cast('|'+cast(e.employeeid as varchar)+'|' as varchar(1000)) as id_path
   from @emp e
   where reportsto is null
union all
select e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo, h.lvl + 1,
  cast(h.name_path as varchar(970))+ cast('|'+e.lastname+','+e.firstname as varchar(30)) as name_path,
  cast(h.id_path as varchar(970))+  cast(cast(e.employeeid as varchar)+'|' as varchar(30)) as id_path
  from @emp e
  join hier h
    on e.reportsto = h.employeeid),

ord_sum as
(select  h.employeeid, h.id_path, count(orderid) as ord_count
   from hier h
    left join @ord o
     on h.employeeid = o.employeeid 
group by h.employeeid, h.id_path),

sub_sum as
(select o1.employeeid, o1.id_path,  
  sum(case when o1.employeeid = o2.employeeid then o2.ord_count else 0 end) as own_count,
  sum(case when o1.employeeid = o2.employeeid then 0 else o2.ord_count end) as sub_count
  from ord_sum o1
   join ord_sum o2
    on charindex(o1.id_path,o2.id_path) = 1
group by o1.employeeid,o1.id_path)

select replicate(' ',lvl*4)+LastName+', '+firstname as [Name], lvl as [level],
  own_count, sub_count, own_count+sub_count as total_count  
  from hier h
  left join sub_sum o
    on h.employeeid = o.employeeid
order by name_path

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.