Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Everybody Reports to Somebody

By Craig Hatley,

Everybody Reports To Somebody

Have you ever thought about the logistics of the organizational structure of a company? Do you know how many layers of management exist from the top to the bottom? I was recently forced to consider these very questions to support a new application that required managers to be able to access data associated with any subordinate employee. This could be an employee that directly reports to the manager or an employee that is several layers below the manager in the overall organizational structure.

For example, if my function received Bob's User ID, it should return the following people based on the sample organizational chart (See Figure 1) . . .

  • Bob
  • Sue
  • Todd
  • Mary
  • Sandy
  • Megan
  • Randy

 Figure 1 


The table that stores our employee data (see figure 2) includes a 'User ID' field which is the primary key for the table and a 'Manager User ID' field which contains the User ID of the manager that each employee directly reports to (everybody reports to somebody philosophy). If Bob has a User ID of '2' in our sample above, Sue, Todd, and Mary would all have a value of '2' in their 'Manager User ID' field indicating that they report directly to Bob (see figure 3).

Figure 2
 Figure 2 


Figure 3
 Figure 3 


Now for the tricky part . . . One of the objectives of our stored procedure is that it should not assume any foreknowledge of the organizational structure including the number of management layers. As I started this project I knew that my code would be required to perform some type of recursion, so I found a great article on the Microsoft web site that discussed recursion in SQL. After spending a little time reading the article, I discovered a couple of potential significant limitations in that SQL recursion is limited to 32 loops (to prevent infinite loop conditions) and most of the examples for passing data back up through the recursive loop chain used a data type that was restricted by a maximum length. Although our company doesn't currently have 32 layers of management hierarchy, I simply didn't want to settle for a 'work around' solution because I suspected it would eventually be an issue for us.

This caused me to start looking for alternatives which after much prayerful consideration yielded the following stored procedure . . .

CREATE PROCEDURE select_subordinates_by_manager
     @UserID int 
As
Set NoCount On

Declare @UserCount int
Set @UserCount = 1

Create Table #In (UserID int) 
Create Table #Out (UserID int) 
Create Table #Result (UserID int) 

Insert Into #In (UserID)      Values (@UserID)
Insert Into #Result (UserID)      Values (@UserID)

While @UserCount > 0
    Begin
        Insert Into #Out (UserID)
        Select UserID From Test..Employee 
        Where ManagerUserID In (Select UserID From #In)
        Select @UserCount = (Select Count(UserID) From #Out)
        If @UserCount > 0
            Begin
                Insert Into #Result (UserID)
                Select UserID From #Out
            End
        Delete From #In
        Insert Into #In
        Select UserID From #Out
        Delete From #Out
    End

Set NoCount Off

Select UserID From #Result
The stored procedure basically starts off by returning a list of the User IDs that report directly to the specified manager User ID. It then runs in a loop checking for User IDs that report to the list of User IDs that were returned in the previous loop until no more User IDs are found. The User IDs returned from each successive loop are added to the temporary table called #Result which is ultimately used to return a list of User ID values representing the subordinate employees.

We typically use the User ID list returned from the previous stored procedure in conjunction with another stored procedure (see below) to return a list representing the employees that report to the specified manager either directly or indirectly.

CREATE PROCEDURE select_subordinate_names_by_manager 
    @UserID int
As
Create Table #Subordinates (UserID int)
Insert Into #Subordinates (UserID)
Exec ('dbo.select_subordinates_by_manager ' + @UserID)
Select e.UserID, e.FirstName, e.LastName 
  From Test..Employee as e
  Join #Subordinates as s on s.UserID = e.UserID
  Order By e.FirstName, e.LastName


After looking at the code and the extensive use of temporary tables you may be concerned about how well this solution will perform. As a baseline, I measured the overall execution time of this technique in a test environment (see environment details below) with a single organizational structure branch that represents 50 layers of management and more than 4500 employees. Needless to say I was pleasantly surprised to discover that the overall execution time was only 64 ms.

Test Environment
  • SQL Platform : Microsoft SQL Server 2000 (Developer Edition)
  • Hardware Platform : Workstation Class PC (Single Pentium 4 3.2Ghz/3GB RAM)

I hope you found this article informative and interesting . . .
Total article views: 5681 | Views in the last 30 days: 5
 
Related Articles
ARTICLE

Selecting from hierarchies like Managers and Employees

Chuck Hoffman shows a technique using sets for selecting records from hierarchies such as Manager / ...

FORUM

select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID]

select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID] --Group by error

ARTICLE

Employee Retention From the Managers Perspective

Part 3 of Steve Jones' series on employee retention, this one focused on the manager's point of view...

FORUM

Report Manager setting missing

Report Manager setting missing

FORUM

user!userID used to build report parameter selection list

  When trying to user user_user!id to limit the input of report paramter's default values I get the...

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones