I have the following table: employees
x
employee emp_id manager manager_id
a 1 x 1
b 2 a 1
c 3 b 2
I am trying to show x 1 is the top manager for a, b and c, even tho a 1 is b 2’s manager and b 2 is c 3’s manager. This is the what I am trying to accomplish:
employee employee_id top_manager top_manager_id
a 1 x 1
b 2 x 1
c 3 x 1
The code I have tried
WITH
report AS
(
SELECT employee, employee_id, top_manager, top_manager_id
FROM employees
UNION ALL
SELECT e.employee, e.employee_id, e.top_manager, e.top_manager_id
FROM dbo.employees e
INNER JOIN report r ON e.employee_id = r.top_manager_id
)
SELECT employee, employee_id, top_manager, top_manager_id
FROM report
Advertisement
Answer
Using recursive CTE:
;WITH all_managers_per_employee (employee_id, manager, manager_id)
AS
(
select employee_id, manager, manager_id from employees -- Anchor member
UNION ALL
select all_managers_per_employee.employee_id, line_manager.manager, line_manager.manager_id -- Recursive member; references cte table, all_managers_per_employee .
from all_managers_per_employee
join employees line_manager on line_manager.employee = all_managers_per_employee.manager
where line_manager.manager <> line_manager.employee
),
tops (employee)
AS
(
select distinct manager as employee
from employees
where manager not in (select employee from employees where manager<>employee)
)
select employees.employee, employees.employee_id, tops.employee as top_manager, all_managers_per_employee.manager_id as top_manager_id
from all_managers_per_employee
join tops on tops.employee = all_managers_per_employee.manager
join employees on employees.employee_id = all_managers_per_employee.employee_id
order by 3,1;