SQL Server - Iterative procedure to get management structure

Question

I have a table in my database that maps a management structure like so.

 staffid    managerid
 14         15
 13         15
 12         15
 11         12
 10         12
 9          10
 8          10
 7          8
 6          8
 5          9
 4          9

So you can see a kind of chain of command where 10 is the manager of 8 and 9, 9 is the manager of 5 and 4, etc.

I would like to see the management structure from a given id's point for view.

So for example, I want to see from the point of view for 8 and I would expect to see something like this: 6, 7, 8, 10, 12, 15

Or for another example from the point of view of 4, I would see: 4, 9, 10, 12, 15

Does that makes sense? - you would only see people who you are a manager of, who is your manager and who is their manager and so on up the line.

Can someone help with the kind of sql that could produce this list. I think I need some kind of iterative function to pass in a id and loop until I find an id with nobody lower in the chain, and then work back up. I'm just not sure how to get started.

Many thanks.

DS

--------------

Thanks to Jaques for supplying what I eventually used to solve my problem of showing staff in a chain of command, and indeed all other who contributed. Much appreciated.

I have a second part now which has come to light, hopefully someone can help me with this also. So for example, I want to see from the point of view for 8 and I would expect to see something like this below:

A given member of staff should be able to change their own manager id, and any manager id for any staff below them in the chain of command.

So for staff id 8 I am looking for something like this:

staffid     managerid      change?
6           8              y
7           8              y
8           10             y
10          12             n
12          15             n
15          N/A            n

For the purposes of being able to implement this, I really just need the change flag to be 'y' if staffid 8 is the staffid or if 8 is in the chain of command for any staffid's below it. In the same way, for a given id of 10 the table would look like this:

staffid     managerid      change?
6           8              y
7           8              y
8           10             y
10          12             y
12          15             n
15          N/A            n

I hope that is clear enough, and again, many thanks in advance.

DS

see http://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy — Jeremy, Jan 09 '15 at 11:55
*Don't* use an iterative procedure. It's the slowest way to do this and obviously doesn't scale beyond a few dozen rows. Add a HierarchyID column, index it and use it to perform child, ancestor, level etc queries — Panagiotis Kanavos, Jan 09 '15 at 11:55
Recursive CTEs aren't needed in SQL Server versions that support HierarchyID. Performance-wise, they are not much better than an iterative approach as logically they are equivalent — Panagiotis Kanavos, Jan 09 '15 at 11:57
marc_s. This question is part of what you stated as the duplicate, but this question requires both sides of the coin, not just downstream, so in essence it is a different question — Jaques, Jan 09 '15 at 12:20
thanks for the contributions guys. i have some new requirements outlined below the dotted line. would much appreciate some thoughts on it. — dstewart101, Jan 12 '15 at 10:59
@dstewart101 You really should post a second, follow-up question instead of reopening this one here… — feeela, Jan 12 '15 at 11:25

score 2 · Accepted Answer · answered Jan 09 '15 at 12:06

Here is a CTE example of what you can use.

DECLARE @staffid int = 8;

WITH n(staffid, managerid) AS 
   (SELECT staffid, managerid
    FROM t1
    WHERE staffid = @staffid
    UNION ALL
    SELECT nplus1.staffid, nplus1.managerid
    FROM t1 as nplus1 
    inner join n on n.staffid = nplus1.managerid
    ),
    m(staffid, managerid) AS 
   (SELECT staffid, managerid
    FROM t1
    WHERE staffid = @staffid
    UNION ALL
    SELECT nplus1.staffid, nplus1.managerid
    FROM t1 as nplus1 
    inner join m on m.managerid = nplus1.staffid
    )
SELECT staffid FROM n
UNION 
SELECT staffid from m

score 0 · Answer 2 · edited May 23 '17 at 10:10

0

You need a recursive CTE for these kind of recursive operations. See this example.

edited May 23 '17 at 10:10

Community

1
1

answered Jan 09 '15 at 11:57

SouravA

5,147
2
24
49

SQL Server - Iterative procedure to get management structure

--------------

2 Answers2