Post reply

Query Help

  • September 15, 2009 at 11:50 am

    #226137

    I need to build a query that gets manger info for an employee, sounds simple, but now it gets interesting. In addition to the field that contains the manager employee_id there is also a field that denotes whether they are a compensation manager. Here is an example of what I'm looking at:

    User Table

    emp_num emp_name emp_manager emp_comp_manager

    1 bob jones 2 no

    2 phil james 3 no

    3 bill hat 4 yes

    4 tim corn 8 no

    So I need to get the emp_num and emp_manager where emp_comp manager = yes.

    For example emp_num 1's boss is emp_num 2 however he is not a com_manager so I need to check emp_num 2's manager to see if he's a comp_manager and so on until I hit a match.

    Any help would be greatly appreciated. All options are open for this since it will be used to generate an extract overnight so no concerns regarding performance.

    Thanks

  • September 15, 2009 at 12:09 pm

    #1054020

    Your sample is a little confusing:

    Is emp_number 3 a com_manager or emp_number 4?

    If either 3 or 4 are managers, how come they have another manager the report to, who's not a com_manager?

    What would really help is if you'd add your expected result...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 15, 2009 at 12:15 pm

    #1054027

    Good question. The comp manager field just means they can manage salaries so for example some managers don't do salary stuff while others can so for example what I would be looking for would be the following:

    emp manger comp_manager

    1 2 3

    2 3 3

    ...

    So for emp 1 his manager is not a comp_manager so we keep looking upwards till we find one. For emp 2 his manager is a comp manager we stop there.

    User Table

    emp_num emp_name emp_manager emp_comp_manager

    1 bob jones 2 no

    2 phil james 3 no

    3 bill hat 4 yes

    4 tim corn 8 no

  • September 15, 2009 at 12:26 pm

    #1054032

    Take a look in Books Online (or MSDN) for "Common Table Expression". Specifically, you want to look at Microsoft's examples for a hierarchy, using a recursive CTE. It's got a great example, and it applies directly to what you're looking for here.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 15, 2009 at 4:02 pm

    #1054158

    Hi ramses2nd,

    GSquared is right you'll want to use a CTE for this one... (I know this because he taught me this once before and I've used it many times in many different flavors... 😉

    :::bowing humbly to GSquared:::

    Here is a suggestion.

    ;with

    CTEMain as

    (

    select...

    from...

    ),

    CTE2 as

    (select *,

    row_number() over (partition by [col.]

    order by [col.]) as Row

    from CTEMain)

    select *

    from CTE2

    where Row = 1;

    I hope this helps!

    -Michelle 🙂

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

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