CODEWITHSUNDEEP
sqlsql-servert-sqlcasehierarchy
gopi nath
gopi nath

Reputation: 196

T-SQL (SQL Server) Complex aggregation inside a Hierarchy

I am trying to write a complex SQL Query to get a sum of a employees Rating for each Team Leader.

I have a similar table like below. 

MngrID EmpID      Rating        IsEMPTL
----------------------------------------
NULL   0          NULL          NULL
0      1          NULL          YES
1      2          3             NULL
1      3          4             NULL
2      8          3             NULL
8      11         3             NULL
0      5          NULL          YES
5      6          4             NULL

In the above example, 1 and 5 area team leaders. 0 is the top most manager. IsEMPTL field is a flag column to identify if the EmpID is a team Lead with a value YES.

The hierarchy in the table is not constant. A child may have another child and that child might have it's own child.

The result expected.

EmpID      TotalRating  IsEMPTL  
---------------------------------
1          13            YES
5          4             YES

I need to write a query that lists all the EmpID where IsEMPTL = 'YES' with a sum of Ratings of of all EmpID's under it. If no rating is provided to any of the EMP's then default value can be 0. If the MngrID is NULL, then don't care about it.

Upvotes: 0

Views: 87

Answers (1)

SqlZim
SqlZim

Reputation: 38043

Using a recursive cte:

;with cte as (
-- anchor elements: where IsEMPTL = 'YES'
  select 
      EmpID
    , MngrID
    , Rating
    , TeamLeader = EmpID
    , IsEMPTL
  from t
  where IsEMPTL = 'YES'
  -- recursion begins here
  union all 
  select 
      c.EmpID 
    , c.MngrID
    , c.Rating
    , p.TeamLeader
    , c.IsEMPTL
  from t c
    inner join cte as p on c.MngrID= p.EmpID
)
select 
    EmpId = TeamLeader
  , TotalRating = sum(Rating)
  , IsEMPTL = max(IsEMPTL)
from cte 
group by TeamLeader

rextester demo: http://rextester.com/BMBC86561

returns:

+-------+-------------+---------+
| EmpId | TotalRating | IsEMPTL |
+-------+-------------+---------+
|     1 |          13 | YES     |
|     5 |           4 | YES     |
+-------+-------------+---------+

Upvotes: 1

Related Questions