How would we write an efficient query to find the manager of a given user within a hierarchy in SQL

We have a standard table of Users containing many fields, but the important aspects are:

ID
UserName
SupervisorID
PositionID

We then have a Positions table which contains the following:

ID
PositionName
PositionTypeID

The PositionTypeID defines our organization levels.  The following are example values
1 - Line Staff
2 - Team Lead
3 - Sr Line Staff
4 - Line Manager
5 - Sr Line Manager

What I'm trying to do is write a query that gives me the list of all Sr Line Staff or lower with their corresponding Line Manager.  This would seem pretty basic, but the problem is, I cannot just assume, for instance, that someone in the Line Staff position type will have a Team Lead or Sr Line Staff above them.  In fact, there could be multiple of each type (i.e., John could be a Line Staff type who works for Amy as a Team Lead who work for Susie as a Sr Line staff who works for Kate as another Sr Line Staff who works for Kevin as the Line Manager.)  This is due to differing departments wanting to utilize the structural levels for different positions, wanting to skip them altogether, or any other reason.  The position table is what separates they differing departmental positions and aligns them with the organizational structure.

Due to this, I cannot just simply say, go 3 levels up to find the Manager of a Line Staff employee.  I have to recurse up the tree to find the first person listed as a Line Manager.  (There could be cases where there are multiple people in the Line Manager position, but for these purposes, I'm going to ignore those situations.)

Is there any reasonably simple and efficient way to write this?  It would be used all over the place in our application, reports, third part systems, etc., therefore, it has to work relatively quickly.  Recursion is the only way I can think of to do this, but even if we can write the query, how well would it perform being run hundreds of thousands of times per day?
Cyprexx ITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
In this case, I think you'd be much better off just storing the resolved hierarchy.  Many people have given this a lot of thought and have done excellent articles on how to do that.  Naturally you'd have to re-capture and re-store it periodically (at least).

You could materialize everyone's hierarchy during off hours.   Then:
1) only use the hierarchy from the previous end-of-day
OR
2) try to maintain the hierarchy capture in real time, every time it changed
OR
3) only re-capture during the demand if it's specifically requested.
1
Pawan KumarDatabase ExpertCommented:
You need to use recursion using cte or a loop or cursor to fetch this kind of data.

Please show few input rows and the expected output.
1
Cyprexx ITAuthor Commented:
Thanks Scott, I think that will be our solution to the speed issue with recursion.  We cache a lot of information, and since the hierarchy doesn't change all that much day to day, this would probably work great for us.

Pawan, the query is the more difficult part.  I have attached an XLSX file with sample data in Sheet1 and expected results in Sheet2.  The results only contain the EmployeeID, EmployeeName, Position, and ManagerName for simplicity, but we could obviously expand upon that.  As I said, we only list out those that are in the Sr Line Staff position or lower in this case, but we could just include everyone and leave the Manager Name NULL if that's easier.  We can figure things out from there of course.  It's the recursion routine that we are finding difficult to write.
SampleHierarchy.xlsx
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
Keep in mind too that recursion is usually a relatively very slow method.  I strongly suspect poor performance when trying to dynamically generate the hierarchies over and over, thousands+ times a day, far slower than is practical to use in the way you need to use it.
0
Pawan KumarDatabase ExpertCommented:
@Author - Are you fine with the recursion or are you changing your design?
0
Cyprexx ITAuthor Commented:
Recursion would be fine if that's really the only way to do it.  If we have to implement recursion, which I anticipate we will, I would follow Scott's advice and simply run a routine once or twice a day to cache all the values down to a flat table so we don't have to run the recursion thousands of times a day.  It's getting the values into this table that would be the hard part.
0
Pawan KumarDatabase ExpertCommented:
This is definitely above the high priority :) A very complex one in recent times. :)

Please use this solution-

;WITH CTE AS
(
	SELECT e.EmployeeID,e.Name,p.Position,e.ParentID FROM tblEmployees e
	INNER JOIN tblPositions	p ON e.PositionID = p.PositionID	
	INNER JOIN tblPositionTypes	pt ON pt.PositionTypeID = p.PositionTypeID
	WHERE pt.PositionTypeID IN (1,2,3)	
)
,CTE1 AS
(
	SELECT e.EmployeeID,e.Position,e.Name,e.Name Name1,e.ParentID , 0 distance FROM CTE e
	UNION ALL
	SELECT * FROM 
	(
		SELECT e.EmployeeID,e1.Position,e1.name,e.name name1,e.ParentID, e1.distance + 1 distance
		FROM CTE1 e1 INNER JOIN tblEmployees e ON e.EmployeeID = e1.ParentID 
	)k	
)
SELECT R.EmployeeID,R.Name,R.Position ,Q.Name1 [Manager Name] FROM 
(
	SELECT * FROM CTE1 e 
	WHERE DISTANCE = 0
)r
CROSS APPLY
(
	SELECT * FROM CTE1 t
	WHERE t.Name = r.Name AND DISTANCE = ( SELECT MAX(DISTANCE) - 1 FROM CTE1 t WHERE t.Name = r.Name )
)q
ORDER BY r.EmployeeID 

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
EmployeeID  Name       Position                     Manager Name
----------- ---------- ---------------------------- ------------
1           Joe        Operational Line Staff       Maria
2           Sue        Research Line Staff          Luis
3           John       Sales Staff                  Rhonda
4           Amy        Operational Team Lead        Maria
6           Harry      Sales Team Lead              Rhonda
7           Pete       Operational Sr Line Staff    Maria
11          Kate       Research Line Staff          Aaron
13          Julie      Sales Staff                  Bob
14          Sarah      Sales Team Lead              Bob

(9 row(s) affected)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cyprexx ITAuthor Commented:
Fantastic!  We will definitely be using the combination of these two solutions.  I'm splitting points based on the amount of effort that went into the query, but still like the idea of caching the results so it only has to run periodically.  Thank you!
0
Pawan KumarDatabase ExpertCommented:
This is a very good question. Thank you.
Learnt something new today.

Cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.