Click here to Skip to main content
11,639,861 members (65,613 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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. Smile | :)

Thanks in Advance
Ranganath P
Posted 21-Mar-11 7:23am
Edited 21-Mar-11 7:26am
v2
Comments
Mika Wendelius at 21-Mar-11 13:26pm
   
Pre tags added

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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 Smile | :)
  Permalink  
Comments
rangiemax at 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. :)
Mika Wendelius at 23-Mar-11 10:17am
   
Just out of curiosity, did you have success with this one?
rangiemax at 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. :)
Mika Wendelius at 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 at 24-Mar-11 4:04am
   
ok i'll try that
rangiemax at 25-Mar-11 2:02am
   
Hi Mika,

I tried that method and got it working. Thanks a lot for your help. :)
Mika Wendelius at 25-Mar-11 2:10am
   
Glad to hear that. You're welcome :)

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

  Print Answers RSS
0 Suvendu Shekhar Giri 270
1 CPallini 177
2 F-ES Sitecore 170
3 jyo.net 142
4 himanshu agarwal 142
0 OriginalGriff 9,081
1 Sergey Alexandrovich Kryukov 8,847
2 Mika Wendelius 7,027
3 Suvendu Shekhar Giri 2,650
4 F-ES Sitecore 2,558


Advertise | Privacy | Mobile
Web03 | 2.8.150731.1 | Last Updated 21 Mar 2011
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100