Post reply

manager - employee relationships

  • May 9, 2011 at 8:43 am

    #1321753

    Check out this link:

  • May 9, 2011 at 12:32 pm

    #1321941

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

  • May 9, 2011 at 12:37 pm

    #1321943

    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/

  • May 9, 2011 at 12:39 pm

    #1321944

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

  • May 9, 2011 at 7:03 pm

    #1322049

    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)

  • May 9, 2011 at 7:49 pm

    #1322053

    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)

  • May 10, 2011 at 2:03 am

    #1322140

    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

  • May 10, 2011 at 7:11 am

    #1322266

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

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

  • May 10, 2011 at 11:22 am

    #1322525

    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 9 posts - 1 through 10 (of 10 total)

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