Multilevel Query Help.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multilevel Query Help....

Author

Topic

mihirpatel83
Starting Member

3 Posts

Posted - 2010-01-16 : 01:48:41

I have a table called Employee with fields and data

EmpID EmpName Collection ManagerID
1 abc 5000 -
2 pqr 5000 1
3 xyz 10000 2
4 lmn 15000 1
5 def 10000 2
6 opq 5000 5
7 jkl 15000 2

and i want something like below as the output from query or stored procedure where in let (abc) be head of department and i want to know all total collection made by respective managers....Manager 5 collected 15000 but he is reporting to manager 2 so his total should be added in total of manager 2 along with other's total who are reporting to manager 2 and so on to the top level...

Output should be like following

EmpID EmpName Collection
1 abc 65000
2 pqr 45000
5 def 15000

I hope to get a reply soon....Needed a quick help...Thanks a lot for help in advance.....

Mihir Patel

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-16 : 05:42:17

Try the following scripts you can get the expected result.

create table EmployeeT (EmpID int,EmpName varchar(5),Collection int,ManagerID int)
insert into EmployeeT values(1,'abc', 5000,NULL)
insert into EmployeeT values(2 ,'pqr', 5000, 1)
insert into EmployeeT values(3,'xyz', 10000, 2)
insert into EmployeeT values(4 ,'lmn', 15000, 1)
insert into EmployeeT values(5 ,'def', 10000, 2)
insert into EmployeeT values(6 ,'opq', 5000, 5)
insert into EmployeeT values(7 ,'jkl', 15000, 2)

Use the following scripts to get the results,

WITH Manager (EmpID, ManagerID, EmpName, Collection,T)
AS
(
-- Anchor member definition
SELECT EmpID, ManagerID, EmpName, Collection, 'E' as T from EmployeeT
UNION ALL
-- Recursive member definition
SELECT EmployeeT.EmpID, EmployeeT.ManagerID, EmployeeT.EmpName,
Manager.Collection , 'M' as T from EmployeeT
INNER JOIN Manager
ON Manager.ManagerID = EmployeeT.EmpID
WHERE NOt(EmployeeT.ManagerID IS NULL)
)

select * into temp# from Manager

insert into temp#
SELECT EmployeeT.EmpID, EmployeeT.EmpID as ManagerID, EmployeeT.EmpName, EmployeeT.Collection, 'E' as T from EmployeeT
where EmpID in (select ManagerID from EmployeeT)

--select * from temp#
select ManagerID,sum(Collection) from temp# where not(ManagerId is null) group by ManagerID

drop table temp#

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources