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')