Solved

Get Hierarchy based on employee id passed to stored procedure

Posted on 2013-06-04
10
314 Views
Last Modified: 2013-06-06
I have two tables: EmployeeMaster and EmployeeDetails. The schema of both are as below:
Schema
Sample data in both tables is shown:

EmployeeMaster
EmployeeDetails
I want to generate the hierarchy using EmployeeDetails table primarily. This table contains a column named: Manager. The EmployeeId of the Manager needs to be picked from the table EmployeeMaster table.

This is how the hierarchy needs to be formed. An EmployeeId is passed as a parameter to a stored procedure. The two supervisors of this Employee needs to be picked and 10 employees below this employee in seniority needs to be picked.

For instance, I pass the EmployeeId of Josh.Berkus to the stored procedure. The stored procedure query should return hierarchy as below:

Hierarchy
I want the final output in this format:

Employee_Id ..... Manager_Id
-----------                 ------------

Please note that Manager_Id is the EmployeeId of Manager.

I tried using a CTE with union all query, but not able to get it correctly.
0
Comment
Question by:rpkhare
  • 4
  • 3
  • 3
10 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39220065
Look at CROSS APPLY there is an example in BOL very similar to this.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 39220094
I tried that but I am not able to pick the two seniors above the Employee and ten juniors below him.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39220136
Please clarify your output...are you expecting 13 rows returned (2 for seniors, the employee, and 10 subordinates) or is it a single record with each of these in its own column?
0
 
LVL 8

Author Comment

by:rpkhare
ID: 39220148
Yes you are right. 13 rows.
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 39220199
First, there is an error in your sample data.
Kevin(4) has manager Robert(5)
There is a record in Employeedetail that shows this (EDID=2).
There is also a record in EmployeeDetail that shows Robert(5) has manager Kevin(4) (EDID=5)
I went by your diagram and deleted the record EDID=5

With the following code you can alter numbers to get more than 2 managers and more than 10 employees if you wish.

CREATE PROCEDURE EmployeeHeirarchy
	@MainEmployeeID int
AS
DECLARE @CurrEmployeeID int, @CurrManagerID int, @Order int
SET @Order=0
SET @CurrEmployeeID=@MainEmployeeID

CREATE TABLE #Heirarchy(Employee_Id int, Manager_ID int, HOrder int)
WHILE (1=1)
BEGIN
	SET @CurrManagerID=NULL
	SELECT @CurrManagerID=em.EmployeeId
		FROM EmployeeDetails ed 
		INNER JOIN EmployeeMaster em ON ed.Manager=em.UserName
		WHERE ed.EmployeeId=@CurrEmployeeID
	INSERT INTO #Heirarchy VALUES(@CurrEmployeeID, @CurrManagerID, @Order)
	IF @CurrManagerID IS NULL BREAK
	SET @Order=@Order+1
	IF @Order > 2 BREAK
	SET @CurrEmployeeID=@CurrManagerID
END

INSERT INTO #Heirarchy
SELECT TOP 10 ed.EmployeeId, em.EmployeeId, -1
FROM EmployeeDetails ed 
LEFT JOIN EmployeeMaster em ON ed.Manager=em.UserName
WHERE em.EmployeeId=@MainEmployeeID

SELECT Employee_Id, Manager_ID FROM #Heirarchy ORDER BY HOrder DESC
DROP TABLE #Heirarchy
GO
EXEC EmployeeHeirarchy 3

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 200 total points
ID: 39220294
Looks like Bear beat me to to it but I was almost done anyway :-)

DECLARE @EmployeeID int

CREATE TABLE #Hierarchy (
      [Rank] int,
      EmployeeID int,
      ManagerID int
      )
      
DECLARE @Manager1ID int,
      @Manager2D int,
      @SubCount int
      
SELECT @Manager1ID = ED1.EmployeeID,
      @Manager2ID = ED2.EmployeeID
FROM EmployeeDetails AS ED0
LEFT OUTER JOIN EmployeeDetails AS ED1
      ON ED0.Manager_ID = ED1.EmployeeID
LEFT OUTER JOIN EmployeeDetails AS ED2
      ON ED1.Manager_ID = ED2.EmployeeID
      
INSERT INTO #Hierarchy ([Rank], EmployeeID, ManagerID)
SELECT 0, EmployeeID, Manager_ID
FROM EmployeeDetails
WHERE EmployeeID = @EmployeeID
      
INSERT INTO #Hierarchy ([Rank], EmployeeID, ManagerID)
SELECT 1, EmployeeID, Manager_ID
FROM EmployeeDetails
WHERE EmployeeID = @Manager1ID

INSERT INTO #Hierarchy ([Rank], EmployeeID, ManagerID)
SELECT 2, EmployeeID, Manager_ID
FROM EmployeeDetails
WHERE EmployeeID = @Manager2ID

INSERT INTO #Hierarchy ([Rank], EmployeeID, ManagerID)
SELECT TOP(@SubCount) -1, EmployeeID, Manager_ID
FROM EmployeeDetails
WHERE Manager_ID = @EmployeeID

SELECT EmployeeID, ManagerID
FROM #Hierarchy
ORDER BY [Rank] DESC

DROP TABLE #Hierarchy
0
 
LVL 8

Author Comment

by:rpkhare
ID: 39223333
@Beartlaoi:

Thanks. I tried it and it is working.
A little help more. I want one more level down. For example, in the hierarchy shown in the last image of my original post, if I have members under Alex, then these must also be visible.

@BriCrowe:

Not yet tried your solution, but sure will give it a try.
0
 
LVL 9

Accepted Solution

by:
Beartlaoi earned 300 total points
ID: 39223505
ALTER PROCEDURE EmployeeHeirarchy
	@MainEmployeeID int
AS
DECLARE @CurrEmployeeID int, @CurrManagerID int, @Order int
SET @Order=0
SET @CurrEmployeeID=@MainEmployeeID

CREATE TABLE #Heirarchy(Employee_Id int, Manager_ID int, HOrder int)
WHILE (1=1)
BEGIN
	SET @CurrManagerID=NULL
	SELECT @CurrManagerID=em.EmployeeId
		FROM EmployeeDetails ed 
		INNER JOIN EmployeeMaster em ON ed.Manager=em.UserName
		WHERE ed.EmployeeId=@CurrEmployeeID
	INSERT INTO #Heirarchy VALUES(@CurrEmployeeID, @CurrManagerID, @Order)
	IF @CurrManagerID IS NULL BREAK
	SET @Order=@Order+1
	IF @Order > 2 BREAK
	SET @CurrEmployeeID=@CurrManagerID
END

SET @Order=-1
WHILE (1=1)
BEGIN
	INSERT INTO #Heirarchy
	SELECT TOP 10 ed.EmployeeId, em.EmployeeId, @Order
	FROM EmployeeDetails ed 
	LEFT JOIN EmployeeMaster em ON ed.Manager=em.UserName
	WHERE em.EmployeeId in (SELECT Employee_Id FROM #Heirarchy WHERE HOrder=@Order+1)
	
	IF @@ROWCOUNT = 0 BREAK
	SET @Order=@Order-1
	IF @Order < -2 BREAK
END

SELECT Employee_Id, Manager_ID FROM #Heirarchy ORDER BY HOrder DESC
DROP TABLE #Heirarchy
GO
EXEC EmployeeHeirarchy 3

Open in new window

0
 
LVL 8

Author Comment

by:rpkhare
ID: 39226640
@Beartlaoi:

Thank you. In the last solution you suggested, is it possible to pass the level as integer to increase the depth of hierarchy downwards?

For example, if I pass 1, then only one hierarchy one level down should be visible. If I pass 2 then two level and if 3 then 3 level.
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 39226873
Yes, it certainly is.  I'll just give you some hints to encourage learning.
Insert your parameter after line 2.
Alter line 34 to use the parameter.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now