Re: Select previous records

From: Ed Prochak <edprochak_at_gmail.com>
Date: Thu, 7 May 2009 18:44:56 -0700 (PDT)
Message-ID: <c4de8863-7d0b-43da-98a5-2c3eed18ae2e_at_e23g2000vbe.googlegroups.com>

On May 7, 8:36 pm, a..._at_unsu.com wrote:
> 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....

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 Received on Thu May 07 2009 - 20:44:56 CDT