Employee and Manager Hierarchy problem
-
I have a table with two fields a jobtitle and reportstopos (manager jobtitle code). I need to get a recursive hierarchy listing so for example, I can put in a manager code (reportstopos) and return back all of the jobtitles that work underneath that code, including employees of my employees. I have sql server 2000 and I am trying to return this information to an asp.net webpage for further processing. I would have no problems setting up stored procedures or temporary tables to solve this problem. HELP
-
If I understand you correctly, you have a "Big Manager" who has "Little Managers" working for her. Under the "Little Managers" are the "Drone Employees". You want not only the "Little Managers" directly under the "Big Manager", but "Drone Employees" as well?
Do you have a reference table with these Manager Codes? Will these reports include the "Big Manager" name? Or will all "Big Mangers" have multiple "Drone Employees" under them?
This strikes me as a reasonable loop using a #TempTable, but we may need more information about the table structure and what you need.
I wasn't born stupid - I had to study.
-
Farrell,
Basically this is the scenario: a user logs into a company webpage and I pull their employee id from the log in, then I send this employeeId to a table (employees) and get their jobtitle code, then I have another table (supervise) with jobtitle and reportstopos. I want to return the list of all subordinates underneath the users jobtitle (little managers and drones). The temp table would store the list of jobtitles underneath the user logged in, this to be used to go back and get employee id's from those jobtitles. So those are the two tables involved. I can include the data from the supervise table if you would like it has about 250 entries.
Thanks
-
The first question in a hierarchy related problem is always this:
Is the hierarchy of fixed maximum depth, or is it variable ?
i.e. How deep is your organisation's org chart ? The optimal solution can't be arrived at until this is known, because you have to code differently as a result (usually with procedural loop/conditional T-SQL code, instead of a single straight set-based SQL query)
-
There is no fixed depth to the hierarchy, but speed and efficiency are not on the top of my list, so it can be written to search say 12 levels deep even if only 7 exist.
-
I am stuck in the middle of a code review and cannot get back to this request. Sorry.
PW has posted many excellent suggestions and will probably figure this out. I would suggest, (I have some similar code somewhere I used for a friend of mine) doing a loop with an ALTER TABLE for the #TempTable to load all the possible surrogates to the UserID. That way, hierarchy should not be too big a problem if you need to add more as you go along...
I wasn't born stupid - I had to study.
-
I would suggest revisiting the data model first.
>>I have another table (supervise) with jobtitle and reportstopos.
Why is Title in this table, and not EmployeeID ? What if a job title changes ? What if 2 people have the same job title "Middle Manager" and they both report to the same person "Big Boss", now you have duplicate rows in the Supervise table, which is really going to mess up any attempted hierarchy traversal code.
Fix the model, don't try to code around it.
-
Doh! Don't you hate getting those kinds of replies? I agree with PW. It sucks to have to do that, but you will be much happier in the long run...
I wasn't born stupid - I had to study.
-
Ok, So I have abandoned my supervise table (since it is redundant and not efficient). I have the following select statement that is doing the first step of what I need: "Select EmployId, LastName, FrstName, A.Jobtitle,reportstopos From employee A, employeeDetail B Where A.Jobtitle=B.Jobtitle And B.reportstopos = @userJob And A.inactive='0' Order By LastName"
This gets me a list of the employees one level below the inputed jobtitle. Now I need it to go at least two levels deeper, so I am thinking another nested select statement or two. But the syntax and approach are beyond my current SQL skill level.
Please advise, also the two tables employee and employeeDetail cannot be altered in anyway they are part of enterprise wide system and I have renamed them here for clarity sake.
-
After a quick conversation with Barrett (is that Grin and Barrett
) - sorry, couldn't help myself.... We determined this little #TempTable structure matches the existing architecture. I made a loop rather than worry about "nesting" the select to levels which may not be necessary. I did not make this to the possible seven or more levels, so this is very simplified. I also set Drone 7 to be Inactive as a simple check. I imagine there may be a more efficient way to handle this, so go to town ladies and gentlemen... 
(If you try 101 or 102, you should be able to see from level two to level four...)
DECLARE @UserJob char(7),
@NextEmployeeID char(15)
SELECT @UserJob = '100' -- assumed this is the EmployeeID of the person logged in...
CREATE TABLE #Employee( EmployeeID char(15),
JobTitle varchar(25),
Inactive char(1))
CREATE TABLE #EmployeeDetail( EmployeeId char(15),
JobTitle varchar(25),
LastName varchar(25),
FirstName varchar(25),
ReportsToPosition char(7))
-- Assumed ReportsToPosition = EmployeeID of the person who is your Boss
INSERT INTO #Employee VALUES( '100', 'BigManager', '0')
INSERT INTO #EmployeeDetail VALUES( '100', 'BigManager', 'Manager', 'Big', '0')
INSERT INTO #Employee VALUES( '101', 'LittleManager1', '0')
INSERT INTO #EmployeeDetail VALUES( '101', 'LittleManager1', 'Manager 1', 'Little', '100')
INSERT INTO #Employee VALUES( '102', 'LittleManager2', '0')
INSERT INTO #EmployeeDetail VALUES( '102', 'LittleManager2', 'Manager 2', 'Little', '100')
INSERT INTO #Employee VALUES( '103', 'Drone 3', '0')
INSERT INTO #EmployeeDetail VALUES( '103', 'Drone 3', 'Employee 3', 'Drone', '101')
INSERT INTO #Employee VALUES( '104', 'Drone 4', '0')
INSERT INTO #EmployeeDetail VALUES( '104', 'Drone 4', 'Employee 4', 'Drone', '102')
INSERT INTO #Employee VALUES( '105', 'Drone 5', '0')
INSERT INTO #EmployeeDetail VALUES( '105', 'Drone 5', 'Employee 5', 'Drone', '101')
INSERT INTO #Employee VALUES( '106', 'Drone 6', '0')
INSERT INTO #EmployeeDetail VALUES( '106', 'Drone 6', 'Employee 6', 'Drone', '102')
INSERT INTO #Employee VALUES( '107', 'Drone 7', '1')
INSERT INTO #EmployeeDetail VALUES( '107', 'Drone 7', 'Employee 7', 'Drone', '101')
INSERT INTO #Employee VALUES( '108', 'Lesser Drone 8', '0')
INSERT INTO #EmployeeDetail VALUES( '108', 'Lesser Drone 8', 'Employee 8', 'Lesser Drone', '106')
-- This gets me a list of the employees one level below the inputed jobtitle.
-- Now I need it to go at least two levels deeper, so I am thinking another nested select statement or two.
SELECT ED.EmployeeID, ED.LastName, ED.FirstName, E.JobTitle, ED.ReportsToPosition
INTO #Presentation
FROM #Employee E
INNER JOIN #EmployeeDetail ED ON( E.Jobtitle = ED.Jobtitle)
WHERE ED.ReportsToPosition = @UserJob
AND E.Inactive = '0'
ORDER BY ED.EmployeeID
SELECT @NextEmployeeID = (SELECT MIN( EmployeeID) FROM #Presentation)
WHILE @NextEmployeeID IS NOT NULL
BEGIN
INSERT INTO #Presentation
SELECT ED.EmployeeID, ED.LastName, ED.FirstName, E.JobTitle, ED.ReportsToPosition
FROM #Employee E
INNER JOIN #EmployeeDetail ED ON( E.Jobtitle = ED.Jobtitle)
WHERE ED.ReportsToPosition = @NextEmployeeID
AND E.Inactive = '0'
AND ED.EmployeeID NOT IN( SELECT EmployeeID FROM #Presentation)
ORDER BY ED.EmployeeID
SELECT @NextEmployeeID = (SELECT MIN( EmployeeID) FROM #Presentation WHERE EmployeeID > @NextEmployeeID)
END
SELECT * FROM #Presentation ORDER BY ReportsToPosition
DROP TABLE #Employee
DROP TABLE #EmployeeDetail
DROP TABLE #Presentation
I wasn't born stupid - I had to study.
-
You can have a look at "Expanding Hierarchies" in BOL. It can give you a few ides on how you might use hierarchies. This is a common question and theres lots written about it that can help.
-Isaiah
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply