Get Hierarchy based on employee id passed to stored procedure

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.
LVL 8
rpkhareAsked:
Who is Participating?
 
BeartlaoiConnect With a Mentor Commented:
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
 
Brian CroweDatabase AdministratorCommented:
Look at CROSS APPLY there is an example in BOL very similar to this.
0
 
rpkhareAuthor Commented:
I tried that but I am not able to pick the two seniors above the Employee and ten juniors below him.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Brian CroweDatabase AdministratorCommented:
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
 
rpkhareAuthor Commented:
Yes you are right. 13 rows.
0
 
BeartlaoiCommented:
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
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
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
 
rpkhareAuthor Commented:
@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
 
rpkhareAuthor Commented:
@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
 
BeartlaoiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.