| Urgent SQl [message #373654] |
Mon, 30 April 2001 10:07  |
J
Messages: 16
Registered: April 2001
|
Junior Member |
|
|
Hi,
I have a promotion hist table
Emplid promotion_dt title
1000 1/1/99 HR
1000 1/1/96 Fin
1000 5/7/87 Phr
1001 1/2/00 Hr
1001 5/6/99 SPHR
1002 2/1/01 PHr
1002 1/1/96 HR.
I want to find who had HR title during FEb 2000.
For ex 1000 had title Hr on 1/1/99 and after that he did not had any title so he holds the title HR during feb 2000
sismilarly 1001 hold HR on 1/2/00.
1002 holds title PHR on 2/1/01
but he should be included since he had title Hr during 1/1/96 to 2/1/01 .
Can anybody help
Thanks
|
|
|
|
| Re: Urgent SQl [message #373720 is a reply to message #373654] |
Wed, 02 May 2001 14:53  |
sundar
Messages: 23
Registered: May 2001
|
Junior Member |
|
|
select emplid,promotion_dt,title from t1 a
where 'HR'=(select upper(title) from t1 b
where promotion_dt =( select max(promotion_dt ) from t1 c
where to_char(promotion_dt,'mmyyyy')='022000'
and b.emplid=c.emplid)
and a.emplid=b.emplid)
and upper(a.title)='HR';
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
