Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

finding hierarchy info Expand / Collapse
Posted Monday, October 21, 2013 1:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 5:36 AM
Points: 1, Visits: 6
I am wanting to run a SQL statement whereby i return the ID of any employee's Director.

The database for employees has a reportsto field which enables me to see the hierarchy of managers above any employee.

There is also a IsDirector flag that indicates a director.

So essentially i want to run sql that would return the first instance of a director in the hierarchy above any employee.

eg if A reports to B and B reports to C (who is a director) then it returns C. (for A's userid)

I basically want the script to run until a director is found.

how would i do this?
Post #1506568
Posted Monday, October 21, 2013 7:31 AM



Group: General Forum Members
Last Login: 2 days ago @ 12:45 PM
Points: 14,982, Visits: 14,888
Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


Need help? Help us help you.

Read the article at for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1506682
Posted Monday, October 21, 2013 11:12 AM


Group: General Forum Members
Last Login: Thursday, October 30, 2014 12:54 PM
Points: 115, Visits: 639
What should we report back if these conditions take place:

1 - The employee in question is a director?
2 - There are several directors at the same level?

Anyway, the idea would be to traverse the hierarchy one level at a time and stop the recursive part as soon as you have found the first 'IsDirector'.

with C1 as (
select employeeid, fname, lname, IsDirector, employeeid as R, IsDirector as FoundDir
from employees
where employeeid = @employeeid
union all
select C.employeeid, C.fname, C.lname, C.IsDirector, P.R, max(IsDirector) over(partition by P.R)
from C1 as P inner join employees as C on C.ReportsTo = P.employeeid
where P.FoundDir = 0
select *
from C1
where IsDirector = 1;

We can adapt it after you have posted DDL and sample data.

Post #1506760
Posted Monday, October 21, 2013 2:23 PM



Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 38,950, Visits: 36,044
This sounds like the perfect task for Nested Sets. Looking forward to readily consumable sample data for this problem.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

Helpful Links:
How to post code problems
How to post performance problems
Post #1506870
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse