Database Challenges

--DaveBallantyne_tsqlchallenge_19_StaticNum.sql

/*
with Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n)-1 FROM Num4),*/
with 
Nums (n) AS (SELECT N  FROM Tsqlc_tally),

cteTree
as
(
   select e.EmployeeID,
          e.FirstName,
          e.LastName,
          e.ReportsTo,
          cast(cast(ROW_NUMBER() over (order by LastName,FirstName) as integer) as varbinary(max)) as HeirarchyOrder,
          1 as Level
    from @emp e
   where e.ReportsTo is null
  union all
  Select e.EmployeeID,
         e.FirstName,
         e.LastName,
         e.ReportsTo,
         cast(cteTree.HeirarchyOrder + cast (cast(ROW_NUMBER() over (order by e.LastName,e.FirstName) as integer) as varbinary(8))  as varbinary(max) )AS HeirarchyOrder,
         Level +1 as Level
    from @emp e        
    join cteTree
     on cteTree.EmployeeID = e.ReportsTo      
),
cteTreeWithOrderCount
as
(

	select cteTree.EmployeeID,
			 cteTree.FirstName,
			 cteTree.LastName,
			 cteTree.HeirarchyOrder,
			 cteTree.Level,
			 cteTree.ReportsTo,
			 COUNT(OrderID) as OrderCount
	  from cteTree
	  left join  @ord o
		on o.EmployeeID =cteTree.EmployeeID
	 group by cteTree.EmployeeID,
				 cteTree.FirstName,
				 cteTree.LastName,
				 cteTree.HeirarchyOrder,
				 cteTree.Level,
				 cteTree.ReportsTo
) 
 ,
cteExplode
as
(
select 	 EmployeeID,
          FirstName,
			 LastName,
			 HeirarchyOrder,
			 Level,
			 OrderCount,
			 Nums.n ,
			 cast(left(CAST(HeirarchyOrder as nvarchar(max)),(nums.n+1)*2) as varbinary(1024))  as PathInc
  From cteTreeWithOrderCount   
  join nums on Nums.n < Level
  
)
,
cteSumUp
as
(

Select EmployeeID,
       FirstName,
		 LastName,
		 HeirarchyOrder,
		 OrderCount,
		 SUM(OrderCount) Over (partition by PathInc) as SumCount,		
		 PathInc,
		 Level
  from cteExplode

)
select replicate(' ',(Level-1)*4)+LastName+', '+FirstName as Name,
       Level-1 as Level,
       OrderCount as BySelf ,
       SumCount - OrderCount as by_sub,
       SumCount as Total
  from cteSumUp 
  where HeirarchyOrder = PathInc
  
 order by HeirarchyOrder
option(MAXRECURSION 0,MAXDOP 1)

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.