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
Author
Message
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
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 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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:45 AM
Points: 115, Visits: 638
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


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
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

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

Add to briefcase

Permissions Expand / Collapse