manager - employee relationships

  • I'm trying to create a recursive cte that can select manager - employee next level down - employee next level down - employee next level down - etc in a heirarchical structure. My efforts below are close, but I can't seem to finish it, any help would be appreciated.

    with EmployeeManagerRelationship (managerid, employeeid, employeelevel, [level], managercontactid, managertitle

    , managerfirst, managerlast, employeetitle, employeefirst, employeelast, employeefirst2, employeelast2)

    as

    (

    select e.managerid, e.employeeid, e.title as employeelevel, 0 as [level], e.contactid as managercontactid

    , CAST(' ' as nvarchar(8)) as managertitle, CAST(' ' as nvarchar(50)) as managerfirst

    , CAST(' ' as nvarchar(50)) as managerlast, c.title as employeetitle, c.firstname as employeefirst

    , c.lastname as employeelast, c.firstname as employeefirst2, c.lastname as employeelast2

    from HumanResources.Employee e

    join Person.Contact c on e.ContactID = c.ContactID

    where ManagerID is null

    union all

    select e.managerid, e.employeeid, e.title as employeelevel, [level] + 1, e.contactid as managercontactid

    , m.title as managertitle, m.firstname as managerfirst, m.lastname as managerlast, c.title as employeetitle

    , c.firstname as employeefirst, c.lastname as employeelast, f.firstname as employeefirst2

    , f.lastname as employeelast2

    from HumanResources.Employee e

    join Person.Contact c on c.ContactID = e.ContactID

    join EmployeeManagerRelationship d on d.employeeid = e.ManagerID

    join Person.Contact m on m.ContactID = d.managercontactid

    join Person.Contact f on f.ContactID = c.ContactID

    )

    select managerfirst + ' ' + managerlast as manager, employeefirst2 + ' ' + employeelast2 as secondlevel

    , employeefirst + ' ' + employeelast as thirdlevel, employeefirst + ' ' + employeelast as fourthlevel

    from EmployeeManagerRelationship

    where managerfirst <> ' '

    order by [level]

    There is an exception to every rule, except this one...

  • Check out this link:

  • I've checked it out thoroughly, but still can't come up with the right query.

    There is an exception to every rule, except this one...

  • You may want to consider scripting out the tables and providing some dummy data.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (5/9/2011)


    You may want to consider scripting out the tables and providing some dummy data.

    I'm using the AdventureWords DB.

    There is an exception to every rule, except this one...

  • I believe you're trying to solve too many problems at the same time. "Divide'n'Conquer". Solve the hierarchy first then join the other tables to get the amplifying information.

    --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)

  • Try this out... and, no... there's no dynamic SQL in it. The bloody forum software just makes it look that way...

    USE AdventureWorks;

    WITH

    cteDirectReports AS

    (

    SELECT EmployeeID, ManagerID, EmployeeLevel = Title, [Level] = 0, ContactID,

    HierarchicalPath = CAST('\'+CAST(EmployeeID AS VARCHAR(10)) AS VARCHAR(4000))

    FROM HumanResources.Employee

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.ManagerID, EmployeeLevel = e.Title, [Level] = d.[Level] + 1, e.ContactID,

    HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.EmployeeID AS VARCHAR(10)) AS VARCHAR(4000))

    FROM HumanResources.Employee e

    INNER JOIN cteDirectReports d ON e.ManagerID = d.EmployeeID

    ),

    cteManagerInfo AS

    (

    SELECT dr.EmployeeID,

    EmployeeFullName = c.FirstName+' '+c.LastName,

    dr.ManagerID,

    m.ContactID,

    ManagerLevel = m.Title,

    dr.EmployeeLevel,

    dr.[Level],

    dr.HierarchicalPath

    FROM cteDirectReports dr

    LEFT OUTER JOIN Person.Contact c ON dr.ContactID = c.ContactID

    LEFT OUTER JOIN HumanResources.Employee m ON dr.ManagerID = m.EmployeeID

    )

    SELECT m.EmployeeID,

    m.EmployeeFullName,

    m.EmployeeLevel,

    m.ManagerID,

    ManagerFullName = c.FirstName+' '+c.LastName,

    m.ManagerLevel,

    m.[Level],

    m.HierarchicalPath

    FROM cteManagerInfo m

    LEFT OUTER JOIN Person.Contact c ON m.ContactID = c.ContactID

    ORDER BY HierarchicalPath

    ;

    --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)

  • It appears that you're trying to flatten the parent child hierachy, I posted something similar here,

    http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspx#bm1073147

    Post 4 on the thread was the solution i finally implemented, It could be a good starting block, its not optimal and I have a couple of Ideas on how to improve it I just need to find the time to do them.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Thanks for your responses! I have what I was looking for with your help.

    There is an exception to every rule, except this one...

  • SQLHeap (5/10/2011)


    Thanks for your responses! I have what I was looking for with your help.

    Outstanding! When you get done, would you post your final solution here, please? Thanks. 🙂

    --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)

Viewing 10 posts - 1 through 9 (of 9 total)

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