Solved

Get Hierarchy based on employee id passed to stored procedure

Posted on 2013-06-04
10
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 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