Re: job for sql, pl/pgsql,gawk,perl or ??

On Aug 28, 2004, at 4:20 AM, Dino Vliet wrote:

> For various reasons I sometimes want only these
> customers and reason as follows:
> "Give me the id's of persons wo start with a
> status="yes" and end with a status="yes". Then I can
> track so called "doubters".
>
> How to do this in postgresql?

If I understand your table schema correctly, you're going to want to do
a couple of self-joins on the table (which I shall call "foo"),
something like:

select id
from foo as foo1
join foo as foo2 using (id, prod, fdate)
join foo as foo3 using (id, prod, fdate)
where
    foo1.stat = 'yes'
    and foo2.stat <> 'yes'
    and foo3.stat = 'yes'
    and foo2.sdate > foo1.sdate
    and foo3.sdate > foo2.sdate;

Something link this will probably work for the yes -> not yes -> yes
situations. However, I think you may also get what you're looking for
with anything that goes from not 'yes' to 'yes'. This covers both the
situation of changing from 'yes' to not 'yes' and back to 'yes' as well
as the case from 'no' to 'yes'.

select id
from foo as foo1
join foo as foo2 using (id, prod, fdate)
where
    foo1.stat <> 'yes'
    and foo2.stat = 'yes'
    and foo2.sdate > foo1.sdate;

Just so you know, this isn't a PostgreSQL-specific issue, but rather an
SQL one. You might want to check out some SQL tutorials on the web or
perhaps pick up a book or two on SQL. I've found Joe Celko's "SQL for
Smarties" helpful.

Good luck!

Michael Glaesemann
grzm myrealbox com