Click here to Skip to main content
15,847,427 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi Everyone,

I'm quiet new to MS SQL and I have problem in creating a hierarchial query that would display an employee and the nearest manager to the topmost manager.

For example, If i have a table with this syntax,

Create table Employee (EmpID int, Manager_ID int NULL, Title nvarchar(30));

with the following values,

1 NULL CEO
2 1    VP
3 2    Senior Manager
4 3    Project Manager
5 4    Developer

For the Employee with ID 5, I need to display data like

5 4 Project Manager
5 3 Senior Manager
5 2 VP
5 1 CEO

The following query will give me only one level of hierarchy ie; 5 4 project manager

WITH ManagerHierarchy (EmpID, ManagerID, Title)
AS
(
-- Anchor member definition
    SELECT e.EmpID, e.ManagerID, e.Title 
    FROM dbo.Employee AS e
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.EmpID, e.ManagerID, e.Title
    FROM dbo.Employee AS e
    INNER JOIN ManagerHierarchy AS d
        ON e.ManagerID = d.EmpID
)
-- Statement that executes the CTE
SELECT EmpID, ManagerID, Title
FROM ManagerHierarchy

Is there any way I can iterate this process so that I can get the Employee's nearest Manager to the topmost Manager?

Any Inputs will be greatly appreciated. :)

Thanks in Advance
Ranganath P
Posted
Updated 21-Mar-11 8:26am
v2
Comments
Wendelius 21-Mar-11 13:26pm    
Pre tags added

1 solution

One way of doing this could be something like:
WITH ManagerHierarchy (EmpID, empid2, ManagerID, Title)
AS
(
-- Anchor member definition
    SELECT e.EmpID, e.EmpID, e.Manager_ID, e.Title 
    FROM dbo.Employee AS e
    WHERE not exists(select 1 from Employee e2 where e2.Manager_ID = e.EmpID) --Manager_ID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.EmpID, null,  e.Manager_ID, e.Title
    FROM dbo.Employee AS e
    INNER JOIN ManagerHierarchy AS d
        ON e.EmpID = d.ManagerID
)
-- Statement that executes the CTE
SELECT EmpID, ManagerID, max(empid2) over (partition by 1), Title
FROM ManagerHierarchy

I reversed the tree to get the last element in the tree as a starting point and then recursively upwards. Now the empid2 contains the employee number if it's the last in chain, otherwise null. And in the calling portion I took max (well it doesn't matter if its min or avg since null's don't count) and to use max overt the whole result set i partitioned it with a constant.

I'm sorry the explanation is quite poor (it's starting get late where I am) but hopefully the code is clearer :)
 
Share this answer
 
Comments
rangiemax 21-Mar-11 14:09pm    
Hi Mika,

Thanks for the quick response. I'll try this query tomorrow morning and let you know if this solved the problem. :)
Wendelius 23-Mar-11 10:17am    
Just out of curiosity, did you have success with this one?
rangiemax 24-Mar-11 0:37am    
umm i got the result set as
5 4 Developer
5 3 Project manager
5 2 Senior Manager
5 1 VP

But it should be
5 4 project manager
.
.
5 1 CEO

I'm still working on the query. Will let you know if i'm able to break this. :) Thanks a lot for help. :)
Wendelius 24-Mar-11 1:15am    
Better yet replace the title with a scalar in the end. Something like:
-- Statement that executes the CTE
SELECT EmpID, ManagerID, max(empid2) over (partition by 1),
(select title from Employee e3 where e3.EmpID = ManagerID)
FROM ManagerHierarchy
rangiemax 24-Mar-11 4:04am    
ok i'll try that

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900