How to get the top boss from list of employess?
-
Hello,
I am looking for t-sql query to get the "top" boss from the list of employee passed as comma delimited string. Can someone please help
My table structure is something like
EmployeeID ManagerID EmployeeName 1 2 Jane 2 3 Joe 3 4 Mary 4 5 Peter 5 null Jack So if I pass the EmployeeID's "1,2,3" (ie Jane, Joe, Mary) to my procedure my result should be the EmployeeID "3" (ie Mary).
I am using the split function to handle comma delimited strings.
Thanks
-
How about this :
set nocount on
declare @Employees table (EmployeeID integer not null, ManagerID integer null, EmployeeName varchar(128))
insert into @Employees values (1, 2, 'Jane')
insert into @Employees values (2, 3, 'Joe')
insert into @Employees values (3, 4 , 'Mary' )
insert into @Employees values (4, 5, 'Peter' )
insert into @Employees values (5, null, 'Jack' )
select * from @Employees
declare @tmpRelations table (HiarchyLevel integer not null, EmployeeID integer not null, ManagerID integer not null)
Declare @CurrentLevel integer
set @CurrentLevel = 1
Insert into @tmpRelations
SELECT @CurrentLevel, EmployeeID, ManagerID
FROM @Employees
where EmployeeID in(1,2,3) -- = @TheBoss
while @@rowcount > 0
begin
set @CurrentLevel = @CurrentLevel + 1
Insert into @tmpRelations
SELECT @CurrentLevel, E.EmployeeID, E.ManagerID
FROM @Employees E
inner join @tmpRelations R
on E.ManagerID = R.EmployeeID
and R.HiarchyLevel = @CurrentLevel - 1
end
-- Cleanup deciples
delete R
from @tmpRelations R
inner join @tmpRelations R2
on R.EmployeeID = R2.EmployeeID
and R2.HiarchyLevel > 1
Select * from @tmpRelations
ps : in your table a person can only have one Manager. Maybe for adaptability a relationstable might be suitable.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you. This query worked great.
-
The solution posted works for the sample data given, but assumes the top manager is one of the employees in the original search set. What if your employee org chart has branches, and you want the lowest-ranking manager that is responsible for a couple of peons from different departments?
This solution first finds out how far down the org chart every employee is, then works from the bottom up until it reaches a level with only one person.
set nocount on
declare @Employees table (EmployeeID integer not null, ManagerID integer null, EmployeeName varchar(25) not null, Depth smallint not null)
insert into @Employees values (1, 2, 'Jane', 0)
insert into @Employees values (2, 3, 'Joe', 0)
insert into @Employees values (3, 4 , 'Mary', 0)
insert into @Employees values (4, 5, 'Peter', 0)
insert into @Employees values (5, 11, 'Jack', 0)
insert into @Employees values (6, 7, 'Asok', 0)
insert into @Employees values (7, 10, 'Dilbert', 0)
insert into @Employees values (8, 10, 'Wally', 0)
insert into @Employees values (9, 10, 'Alice', 0)
insert into @Employees values (10, 11, 'PHB', 0)
insert into @Employees values (11, null, 'CEO', 0)
Declare @CurrentLevel integer
set @CurrentLevel = 0
-- Define the number of levels between each employee and the highest-ranking manager
while @@rowcount > 0 begin
set @CurrentLevel = @CurrentLevel + 1
update e1 set Depth = @CurrentLevel
from @Employees e1
inner join @Employees e2 on e1.ManagerID = e2.EmployeeID
where e2.Depth = @CurrentLevel - 1
end
select * from @Employees
declare @Subset table(EmployeeID integer not null, ManagerID integer null, Depth smallint not null)
insert into @Subset(EmployeeID, ManagerID, Depth)
select EmployeeID, ManagerID, Depth from @Employees where EmployeeID in (1,2,6)
declare @MinLevel smallint
-- Start at the level of the lowest-ranking employee
select @CurrentLevel = max(Depth) from @Subset
-- Must look at least to the level of the highest-ranking employee
select @MinLevel = min(Depth) from @Subset
if (select count(*) from @Subset) > 1
begin
while 1=1
begin
-- Add the next-level managers to the subset
insert into @Subset(EmployeeID, ManagerID, Depth)
select distinct e.EmployeeID, e.ManagerID, e.Depth
from @Employees e
inner join @Subset s on e.EmployeeID = s.ManagerID and s.Depth = @CurrentLevel
set @CurrentLevel = @CurrentLevel - 1
-- Stop when only one top-level manager is found
-- or when no higher-ranking managers remain
if @CurrentLevel = 0 or (@CurrentLevel <= @MinLevel and
(select count(distinct EmployeeID) from @Subset where depth = @CurrentLevel) = 1)
break
end
end
select * from @Employees where EmployeeID IN
(select EmployeeID from @Subset where Depth = @CurrentLevel)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply