Solved

Get Hierarchy based on employee id passed to stored procedure

Posted on 2013-06-04
10
310 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL server Varchar and nvarchar, GMT_DATE 23 50
SQL Help - 12 42
Report Builder 9 31
Sql query to Stored Procedure 6 20
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

747 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

13 Experts available now in Live!

Get 1:1 Help Now