?
Solved

Employee hierarchy tree

Posted on 2011-05-04
4
Medium Priority
?
778 Views
Last Modified: 2013-11-26
Hi have a stored procedure that works perfectly in getting everyone in a hierachy but want to find a way of seeing where a person is so that i can put it in a tree view.

eg Director will be one and HOD could be level 2  and manager could be level 3 and people reporting to him could be level 4 depending on how many levels there are. Have attached the sql code that im using
@Root nvarchar(50) 
	
AS
 
  WITH Children(StaffName,StaffNumber)
			AS(
			SELECT Distinct StaffName,StaffNumber
			From StaffADITSLookup where SeniorNumber = @Root
			UNION all
			SELECT m.StaffName,m.StaffNumber FROM StaffADITSLookup m 
			INNER JOIN children l
			ON l.StaffNumber = m.SeniorNumber 
			)


SELECT Distinct StaffName ,StaffNumber FROM Children
SET NOCOUNT ON 

	RETURN

Open in new window

0
Comment
Question by:wolivier69
  • 2
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35689666
try
WITH Children(StaffName,StaffNumber, Level)
			AS(
			SELECT Distinct StaffName,StaffNumber, 1 as Level
			From StaffADITSLookup where SeniorNumber = @Root
			UNION all
			SELECT m.StaffName,m.StaffNumber, l.Level + 1 as Level
                        FROM StaffADITSLookup m 
			INNER JOIN children l
			ON l.StaffNumber = m.SeniorNumber 
			)

Open in new window

0
 

Author Comment

by:wolivier69
ID: 35689819
Thanks for the effort but its not working getting the wrong output. Only one person in the case tested should be at level 2 but have a few and the person who should be is at one
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35689835

Doesn't this depend on the value of @Root

If you pick root to be a person who is in level two, then all levels are shifted
0
 

Author Closing Comment

by:wolivier69
ID: 35689888
Thanks much appreciated worked properly after testing out again
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 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