|
|
Posted by Bill on 10/12/06 16:37
Alex,
It looks like CELKO gave you the answer along with a lot more
"advanced" advice. I hope you appreciate that I was trying to give you
a nudge to let you figure it out.
If you are still having trouble, post your SQL and we can make any
corrections.
Bill
alex wrote:
> Hello experts.
>
> I'm a novice sql writer and need some help in writing a query to
> extract applicable data from the following table (titled EMPLOYEE):
>
> --
> ID_NUMBER CODE DATE
> ------------------ --------- --------
> 12 VO 20060914
> 12 XD 20060913
> 12 AD 20060912
> 12 WR 20060911
> 12 AT 20060910
> 45 VO 20060914
> 45 XR 20060913
> 45 AT 20060912
> 45 AD 20060911
> 45 AT 20060910
> 78 AD 20060914
> 78 AT 20060913
> 78 VO 20060912
> 78 AD 20060911
> 78 AT 20060910
>
> I need to select ID_NUMBER
> from EMPLOYEE
> where CODE = 'VO'
>
> caveat: I only want the ID_NUMBER(s) where the CODE = 'VO'
> and the previous CODE (by DATE) = 'AD'
> or the previous CODE (by DATE) = 'AD' with any CODE in between
> except 'AT';
>
> E.g., in the above example, the appropriate code should select
> ID_NUMBER(s) 12 and 78 because
> 1. a VO code exists
> 2. an AD code (by DATE) precedes it
> 3. although 'AD' does not come immediately before 'VO' (in the
> case of ID_NUMBER 12) 'AT' cannot be found in between
>
> I hope I haven't confused anyone. Any help would be appreciated.
Navigation:
[Reply to this message]
|