Click here to Skip to main content
12,694,121 members (35,009 online)
Rate this:
 
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. :)

Thanks in Advance
Ranganath P
Posted 21-Mar-11 8:23am
Updated 21-Mar-11 8:26am
v2
Comments
Mika Wendelius 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 :)
  Permalink  
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. :)
Mika 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. :)
Mika 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
rangiemax 25-Mar-11 2:02am
   
Hi Mika,

I tried that method and got it working. Thanks a lot for your help. :)
Mika Wendelius 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.170118.1 | Last Updated 21 Mar 2011
Copyright © CodeProject, 1999-2017
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