|
View previous topic :: View next topic
|
| Author |
Message |
sudhakarraju
New User
Joined: 05 May 2005
Posts: 31
|
|
|
|
I have the below sample table
| Code: |
¦---¦-----------¦----¦
¦EMP¦ DOS ¦AMT ¦
¦---¦-----------¦----¦
¦10 ¦2009-01-01 ¦10 ¦
¦10 ¦2010-03-01 ¦21 ¦
¦11 ¦2010-01-01 ¦25 ¦
¦11 ¦2010-02-01 ¦23 ¦
¦12 ¦2010-02-28 ¦25 ¦
¦13 ¦2010-02-28 ¦10 ¦
¦13 ¦2010-03-01 ¦12 ¦
¦14 ¦2009-01-01 ¦11 ¦
¦14 ¦2009-02-01 ¦10 ¦
----------------------
|
I need all the entries where the employee has more than one DOS
and atleast one of the DOS should be in the past one week.
in the above table EMP - 10 & 13 entries should only be in the result. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
past one week = last seven days ?
1) all entries = all emp no :
| Code: |
select emp from tab1 A
where exists
(select * from tab1 b
where a.emp = b.emp and b.dos <> a.dos)
and a.dos > current_date - 7 days
group by emp |
2) all entries = all rows:
| Code: |
select * from tab1 A
where exists
(select * from tab1 b
where a.emp = b.emp and b.dos <> a.dos)
and exists
(select * from tab1 c
where a.emp = c.emp
and c.dos > current_date - 7 days) |
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
This can also work.......
| Code: |
select week(dos), dos from emptab
where week(dos) = week(current date) - 1; |
Sushanth |
|
| Back to top |
|
|
|
|
