| Author |
 Topic  |
|
|
jaycee
Starting Member
United Kingdom
14 Posts |
 Posted - 11/29/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/29/2012 : 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
United Kingdom
14 Posts |
Posted - 11/29/2012 : 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) |
|
|
| |
Topic |
|
|
|
SQL for layers of managers
