Hi,

Hoping to do this in one query without writing code.

Got an order table:

Customer ID
Order Date
Status

Customer places an order, status becomes 'Active' and 1 year later the
customer can re-new or cancel. If customer renews then current order
goes to 'Complete' and new order is placed with 'Active'.

I need to know, for any given date, is current 'Active' order result
of a 'renew'. So, is there an order 1 year ago.

Not sure how this is done, maybe with some analytical stuff?
Basically take order from now, look back 1 year and see if there is an
order there.....

Possible without PL/SQL I hope....

On May 7, 8:36*pm, a... (AT) unsu (DOT) com wrote:

Come on now think it out. Do it in parts.

how would you select results to satisfy this requirement?
for any given date, is current 'Active' order

So then what is a "renew" is?
....
that there EXISTS a previous one.

So what makes it the Previous one?

You should be able to do something along the lines of this:

select OT1.order, OT1.customer
from yourOrderTable OT1
where orderdate somehow_related_to givendate
and exists ( select 1 from yourOrderTable OT2
where OT2.orderdate < OT1.orderdate
and OT2.customer = OT1.customer )

Should be no PL/SQL needed.

Ed

<art (AT) unsu (DOT) com> a écrit dans le message de news: 8ea8aea2-4a21-461e-89cc-c7fffaa50bf6...oglegroups.com...
To know if it is possible you have to post a test case:
create table and insert statements as well as the result you want with these data.

And you don't tell us what happens if customer cancels.

But as there are at least 2 operations, one update and one insert I don't see
how you can do it without a programming language.

Regards
Michel

On May 7, 5:36*pm, a... (AT) unsu (DOT) com wrote:
Well, you could just say trunc(order_date) - 365 or whatever, but of
course, that might not be a business day, so you need some way of
knowing what the possible business dates or windows are one year ago,
and don't forget about leap years and is this just for one political
unit or can you have different holidays and can customers be related
or buy each other or multiply subscribe and what if this all dies in
the middle and what if the check is in the mail already and...

So, what is a customer, what is a year, what is an order, what is
analytical stuff, what is code? Interesting business day solutions
out there with some googling.

I have been absolutely floored [geek joke intentional] with how really
bad some of the magazine renewals have been, including one of my
favorite magazines I've been charter subscribed since the early '80's
never cashing my check at least 3 times in recent years.

jg
--
@home.com is bogus.
Occifer, I hav a rite to C the sour code! http://www.twincities.com/news/ci_12267906?source=rss