I was looking for a more accurate way to locate a top level item in any hierarchy without having to do a lot of extraneous looping.  The solution I found was is efficient and easy!

For this example, we will determine the top level manager for any employee, regardless of the level of their managerial hierarchy.  For this test, I have created a table called employees:

Employees

We can use the following method to determine an employee’s top level manager and their relationship level:

USE [Test]GO

DECLARE @EmployeeId int = 13 — Christian Lewis
;WITH EmployeeHierarchy
AS
(
SELECT
e1.EmployeeId
, e1.EmployeeName
, e1.ManagerId
, [Level] = 0
FROM
Employees e1
WHERE
(EmployeeId = @EmployeeId)
UNION ALL
SELECT
e2.EmployeeId
, e2.EmployeeName
, e2.ManagerId
, [Level] + 1
FROM
Employees e2
INNER JOIN EmployeeHierarchy ON e2.EmployeeId = EmployeeHierarchy.ManagerId
)
SELECT
*
FROM
EmployeeHierarchy e3
WHERE
e3.[Level] = (SELECT MAX([Level]) FROM EmployeeHierarchy)

Which will result in the following output:

Query-Result

Neat!

Advertisements