Employee hierarchy tree

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

wolivier69Asked:
Who is Participating?
 
Ephraim WangoyaCommented:
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
 
wolivier69Author Commented:
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
 
Ephraim WangoyaCommented:

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
 
wolivier69Author Commented:
Thanks much appreciated worked properly after testing out again
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.