SQL for layers of managers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL for layers of managers

Author

Topic

jaycee
Starting Member

18 Posts

Posted - 2012-11-29 : 13:00:46

I have a table of employees with fields of employeeid, managerid. Each user has a manager and each manager has a manager to a number of levels, so:
employeeid, managerid with content:
1, 5
2, 5
3, 7
4, 8,
5, 10
6, 5
etc and going on for an unknown number of manager levels. I need to get out a Manager of any level who can have employees/managers beneath him who if managers, have their own employees under them. All levels need to be returned.
Can anyone help with the best way to do this?
Any help gratefully rec'd.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 13:16:26

A recursive CTE would be the best way to do this. The MSDN page has documentation and an example which is very similar to the problem you are trying to solve: http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Take a look at the example there, and if need help tweaking it for your purposes, please reply?

jaycee
Starting Member

18 Posts

Posted - 2012-11-29 : 17:25:12

Hi, Many thanks for the pointer. I found the examples there hard work to follow and found the ones here are a more basic and easier to follow set of details: http://www.4guysfromrolla.com/webtech/071906-1.shtml I will try the below on the db at work and confirm if it does the job:
WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ManagerID, HierarchyLevel) AS
(
-- Base case
SELECT
EmployeeID,
LastName,
FirstName,
ManagerID,
1 as HierarchyLevel
FROM myEmployees
WHERE employeeid=285--ManagerID IS NULL (Replaced to enter ID of Manager that wants to run the report.)

UNION ALL

-- Recursive step
SELECT
e.EmployeeID,
e.LastName,
e.FirstName,
e.ManagerID,
eh.HierarchyLevel + 1 AS HierarchyLevel
FROM myEmployees e
INNER JOIN EmployeeHierarchy eh ON
e.ManagerID = eh.EmployeeID
)

SELECT *
FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName
OPTION (Maxrecursion 10000)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources