query help required

Question

query help required

Viewing 8 posts - 16 through 22 (of 22 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Nabha (2/10/2010)
Maybe I'm missing something but could it be as simple as this?
Regards,
Willem
http://wschampheleer.wordpress.com
That would give the records even if the employee is not active now.
and the employee should be active on the current date(i.e today)

I'm not so sure that's true. According to the specs the OP gtave, each employee will only have one row at a time where ACT = 'Y' or no rows.

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 2

Jeff Moden (2/10/2010)
Nabha (2/10/2010)
Maybe I'm missing something but could it be as simple as this?
Regards,
Willem
http://wschampheleer.wordpress.com
That would give the records even if the employee is not active now.
and the employee should be active on the current date(i.e today)
I'm not so sure that's true. According to the specs the OP gtave, each employee will only have one row at a time where ACT = 'Y' or no rows.

Thanks Jeff. I noticed it too but did not bother to comment on it.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

wschampheleer SSCertifiable Points: 5504 More actions · Answer 3

Paarthasarathy (2/10/2010)
Yet another way 🙂
select A.* from emp A
where A.datett=(select max(C.datett)
from emp C
where A.eid = C.eid
and C.datett <= '06/06/2006'
)
and exists
(
select '' from emp B
where A.eid = B.eid
and B.act='Y'
)

Paarthasarathy, FYIO: I compared your query to mine and seems like mine performs better on your sample set (38% compared to 62%). The aggregation (max) in your query being the culprit.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

Jeff Moden (2/10/2010)
Nabha (2/10/2010)
Maybe I'm missing something but could it be as simple as this?
Regards,
Willem
http://wschampheleer.wordpress.com
That would give the records even if the employee is not active now.
and the employee should be active on the current date(i.e today)
I'm not so sure that's true. According to the specs the OP gtave, each employee will only have one row at a time where ACT = 'Y' or no rows.

Yes, I misunderstood the requirement Jeff. THanks. 🙂

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Sorry, Nabha. Your code certainly looks like it'll work just fine. Considering the data spec, I was just wondering why you went the extra mile with the correlated sub-query instead of a direct query with no sub-queries.

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 6

Jeff Moden (2/11/2010)
Sorry, Nabha. Your code certainly looks like it'll work just fine. Considering the data spec, I was just wondering why you went the extra mile with the correlated sub-query instead of a direct query with no sub-queries.

ah ok. I am sure you'd have got it by now, OP wanted the 'latest' record as on '06/06/2007' where the current status is active. Initially I missed the point about only one record with the status as 'y'.

I thought there would be multiple records with act status as 'y' (for an employee) and my logic of finding out the 'active' employee was to order by the datett (CTE) and check the status for value 'Y' for the first record (sub query).

Thanks Jeff, (my work here did not go unnoticed :-D)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Heh... OK... got it. You write nice tight code all the time and I was just wondering if you saw something that I was missing.

To be sure, I always enjoy "Nabha posts" and your work get's noticed a lot whether you realize it or not. 🙂

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 8

Jeff Moden (2/11/2010)
To be sure, I always enjoy "Nabha posts" and your work get's noticed a lot whether you realize it or not. 🙂
By
--Jeff Moden

Ohh!!! There cant be a bigger compliment than this for me Jeff. :w00t:

(Learnt a lot from you) :satisfied:

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