Query Help

Question

Post reply

Query Help

ramses2nd

Hall of Fame

Points: 3720
More actions
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

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

mm-1009269 SSCommitted Points: 1559 More actions · Answer 4

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 🙂