March 21, 2011 at 11:27 pm
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,
EmpID ManagerID Title
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
EmpID ManagerID Title
5 4 Project Manager
5 3 Senior Manager
5 2 VP
5 1 CEO
For the employee ID 4,
EmpID ManagerID Title
4 3 Senior Manager
4 2 VP
4 1 CEO
and so on.
The following query will give me only one level of hierarchy ie;
EmpID ManagerID Title
1 NULL CEO
2 1 VP
3 2 Senior Manager
4 3 Project Manager
5 4 Developer
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 each employee's nearest Manager to the topmost Manager?
Any Inputs will be greatly appreciated. 🙂
Thanks in Advance
Ranganath P
March 22, 2011 at 9:58 am
Kindly provide the DDL and DML used to construct the environment in your example.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2011 at 11:00 am
The problem is that you have this in your Anchor:
WHERE ManagerID IS NULL
That means it will only get people who don't have a manager. Then, your recursive portion tries to find the managers of people who, by definition, don't have managers.
You need to change the anchor so that it's getting the employee you want to start from.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2011 at 1:28 am
may be you can use this
Declare @mgrid int,@empid int
set @empid=234
declare @hir table ( empid int,mgrID int, title varchar(255))
select @mgrid= ManagerID from dbo.Employee where EmployeeID= @empid
while (@mgrid is not null)
BEGIN
insert into @hir
Select @empid,ManagerID,Title from dbo.Employee where EmployeeID=@mgrid
Select @mgrid=ManagerID from dbo.Employee where EmployeeID=@mgrid
END
Select * from @hir
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply