• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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.
0
rpkhare
Asked:
rpkhare
  • 4
  • 3
  • 3
2 Solutions
 
Brian CroweCommented:
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
 
Brian CroweCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 CroweCommented:
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
 
BeartlaoiCommented:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now