Pretty easy one if I understand you correctly

select max(rate) as rate
from rtemas
where EFFECTIVEDATE<=??
and RATEID=??

You could make a SP for this if it gets used too often.



--- In firebird-support@yahoogroups.com, "Cao Ancoinc" <groups@a...>
wrote:

> 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

>
> Many thanks
> Cao