Employee Report with hierarchical level

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 891
sometimes, we need to list out the employee details along with their hierarchical level.

The below query lists out the employee details with his/her manager name/ID and level
WITH emp_report AS 
(SELECT a.employee_id, A.job_id, a.first_name, a.manager_id, 0 AS report_level
FROM employees a WHERE a.manager_id is null
UNION ALL
SELECT a.employee_id, A.job_id, a.first_name ,a.manager_id, report_level + 1 AS report_level
FROM employees a JOIN emp_report B ON (a.manager_id = b.employee_id)
)
SELECT * FROM emp_report;

Comments or Responses

Login to post response