How is the given query correct as it is using T1 inside the with clause and T1 is declared after the clause within WITH is completed.
WITH T1(Emp,Manager,Salary) AS
(
SELECT tt2.[Emp],tt2.[Manager],tt2.[Salary]
FROM [YourTable] AS tt1
RIGHT OUTER JOIN [YourTable] AS tt2 ON tt1.[Emp]=tt2.[Manager]
WHERE tt1.[Emp] is NULL
UNION ALL
SELECT r.[Emp],T1.[Manager],r.[Salary]
FROM [YourTable] AS r
INNER JOIN T1 ON r.[Manager]=T1.[Emp]
)
SELECT [Manager],SUM([Salary]) AS Salary
FROM T1
GROUP BY [Manager]
ORDER BY SUM([Salary]) DESC
Above query is answer to following question -
I have table with columns (Employee, Manager, Salary). Need to calculate aggregate salary for all employees corresponding to top-level managers in one SQL. For example
Input table is :
Emp Manager Salary
A T 10
B A 11
C F 13
D B 5
Result should be :
Top-Lvl Manager Salary(agg)
T 26
F 13
Manager-Employee layering can go multiple levels.
This is a recursive query. The part before UNION ALL gets the base records. The part after it recursively gets more rows attatched to the former.
The first part is confusingly written. It is an anti-join pattern inplemented even with a right outer join which many consider hard to read. It merely means this:
select emp, manager, salary
from yourtable
where manager not in (select emp from yourtable);
So you get all employees that have no manager (i.e. the super managers).
With the part after UNION ALL you get their subordinates and the subordinates of these etc. A hierarchical query.
At last in
SELECT [Manager],SUM([Salary]) AS Salary
FROM T1
GROUP BY [Manager]
ORDER BY SUM([Salary]) DESC
you use those rows to get a cumulated salary per manager.
You can read up on recursive queries in SQL Server here: https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx.