SQL Hierarchy Pointers

Question

SQL Hierarchy Pointers

fryere

Ten Centuries

Points: 1054
More actions
January 27, 2003 at 9:00 am

#79991

Imagine a table called Employees with the following flds:
EmpNum, EmpName, MgmtLevel, DirectSupervisor
EmpNum = Employee ID Number
EmpName = Employee Name
MgmtLevel = Level of Management (CEO, Vice Pres., Dept Mgr, Sect Mgr, Emp)
DirectSupervisor = EmpNum of Supervisor.
I want to be able to Sum all of the workers that fall under an employee’s supervison.
For example a dept mgr may have a 3 sections with 10 workers under each section. Therefore the total # of workers that falls under this dept mgr is 30. I want to be able to get the info from any MgmtLevel and I would perfer to keep all the employees in one table. I am currently getting the data through a Stored Proceedure that updates Total tables for each MgmtLevel, but I was hoping there was a way to pluck this info out with one SQL statement.
I Hope this makes sense.
Any Ideas?
R Frye
fryere

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12541 More actions · Answer 1

I think the following query solves your problem :



SELECT e.EmpNum, e.EmpName, s.NrSupervise

FROM Employees e

     LEFT OUTER JOIN 

     (SELECT DirectSupervisor AS DS,

             count(*) AS NrSuperVise

      FROM Employees

      GROUP BY DirectSupervisor) s

     ON e.EmpNum = s.DS

This will return the count for ALL employees, even those that don't supervise anyone.

Change LEFT OUTER JOIN to INNER JOIN to get only results for people who do supervise someone.

fryere Ten Centuries Points: 1054 More actions · Answer 2

Thanks NPeters,

You gave me exactly what I asked for, unfortunately, after I looked at this I need something slightly different. I would like to pass an EmpNum to the SQL statement and have it to return a list of EmpNums that are under it. (All the way down the chain) I can only get it to return the one level below.

R Frye

fryere

XMA Newbie Points: 1 More actions · Answer 3

I guess you can use @@rowcount, thanks to this script.

The NOT IN can be replaced by a left outer join, which could be a little faster.

BEGIN

Declare @AnyMgtId Int -- a Manager Id

select @AnyMgtId= --value

CREATE TABLE #TempEmp(EmpId int)

INSERT #TempEmp(EmpId) VALUES (@AnyMgtId)

WHILE @@rowcount!=0

BEGIN

INSERT #TempEmp(EmpNum)

SELECT EmpNum

FROM Employees,#TempEmp

WHERE DirectSupervisor = #TempEmp.EmpId

AND EmpNum NOT IN (SELECT EmpId FROM #TempEmp)

Select EmpId From #TempEmp

END

fryere Ten Centuries Points: 1054 More actions · Answer 4

Thanks for the help. I have it working By updating MgmtLevel tables. I thought there might be a easy way to do it with a SQL Statement.

fryere