On 15 Oct 2001, at 19:19, Doug Chamberlin wrote:

> At 10/15/2001 06:53 PM (Monday), Paul Schmidt wrote:
> > > Let's suppose I have a SALES_DETAIL table with these records:
> > >
> > > Product_ID Date Price
> > > ACA13003 2001/09/14 145.00
> > > ACA13003 2001/09/23 155.00
> > > ACA13003 2001/10/06 152.00
> > >
> >
> >I don't have a solution but a similar problem ....
> >Suppose we add another record
> >ACA13003 2001/10/31 162.50
> >Today Oct 15th we want the effective price, which should be the
> >one from 2001/10/06?
>
> If by effective price you mean the most recent one which is listed as
> today or earlier, I think just adding just such a restriction should
> do it:
>
> SELECT Price
> FROM SALES_DETAIL
> WHERE Product_ID='ACA13003'
> AND Date= (SELECT MAX(Date)
> FROM SALES_DETAIL
> WHERE Product_ID='ACA13003' AND
> Date <= 'TODAY')
>

I think that would do it, should probably be an index on the
Product_ID and Date.

Paul


Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com