I have the following table: employees
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;