Cao Ancoinc schrieb:

> Hi All
> I hope there are some gurus who could help with this
>
> I have the following abridged table
>
> Rate Table ->RteMas
> RATEID EFFECTIVEDATE RATE
> ===================================
> RATE1 01/10/2003 1.00
> RATE1 01/11/2003 1.50
> RATE1 01/12/2003 2.00
> RATE2 01/11/2003 3.00
> RATE2 01/12/2003 3.50
>
> I need to find the Rate given 2 parameters
> RATEID
> ORDERDATE
>
> I must return the RATE where the EFFECTIVEDATE is not larger than ORDERDATE
> and
> EFFECTIVEDATE<=ORDERDATE
>
> ie the highest EFFECTIVEDATE not larger than ORDERDATE
>
> eg given RATEID=RATE1 and ORDERDATE=15/11/2003 should return RATE=1.50

SELECT FIRST 1 RATE FROM RteMas

WHERE
RATEID = :RATEID AND
EFFECTIVEDATE <= :YOURDATE

ORDER BY EFFECTIVEDATE DESC


Luc.