SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL for layers of managers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 11/29/2012 :  13:00:46  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/29/2012 :  13:16:26  Show Profile  Reply with Quote
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?
Go to Top of Page

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 11/29/2012 :  17:25:12  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000