Re: sql help to find next record of same status

jeffchirco_at_gmail.com wrote:
> Ok, I have a table as described that is a list of employee's status.
> Whether it is "A" for Active, "T" for terminated, or "L" for being on
> leave and the date they went on that status.
> empno number(10),
> eff_date date,
> status varchar2(1)
>
> I need to find all the records where an employee went from status "A"
> to status "A" again (they were not terminated or on leave between their
> two active status). Don't know how this could have happend, but we
> just need to make sure it has not.
> How can I write a SQL statment to do this. I'm lost. Thanks.
>
> Jeff

SELECT all records that meet your first criterion. SELECT all of the records that meet your second criterion. Put parentheses around it (make an in-line view) and find the intersection between the two sets.

Though likely your dataset is small enough you could just do it with a cursor loop (8i or before) or array processing (9i or above) just as quickly.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)