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

[Dino Vliet]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Michael Glaesemann]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org