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

Dear all,

I have this problem with getting information out of my
database and I don't know if it is my knowledge of sql
or that this is something that can't be done in sql.

I have the following table:

id  fdate        prod  price    stat  nr_items  sdate
x1 23-11-2003    123   456      yes     7     01-11-03
x1 23-11-2003    123   456      may     4     07-11-03
x1 23-11-2003    123   400      yes     7     14-11-03
x2 29-11-2003    201   711       no     6     01-11-03
x2 29-11-2003    133   700       no     6     08-11-03

Here, you can see that id x1 is interested in product
123 that we offer for 456 euro's and that he's sure
and wants 7 items. Then almost 6 days afterwards he
mails us to change his order to 4 items and after we
arrange a price reduction.

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?
So basically I want to be able to scan a file, order
it by id,sdate and the want to look at the field stat.
If I see that the contents of the field changes from
yes to something else and then back to yes, I select
the id. Other positives would be stat started from no
and ending in yes.

The total number of changes an id can have varies.

Can this be done in sql? Or is this a pl/pgsql task?
In that case, what should I think of? Another option
for me is to output a textfile and try to do it in
gawk or perl or something else.




__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail

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