finding hierarchy info

Question

Post reply

finding hierarchy info

evvo1961

SSC Journeyman

Points: 82
More actions
October 21, 2013 at 1:14 am

#281594

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Hunchback SSCrazy Points: 2649 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)