Cao Ancoinc 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
>
>
>
>
>

select first 1 rate from myTable where efectivedate <=MyOrderDate
and rateid = MyRateId
order by efectivedate desc

Adomas