How to get the Amount according to the Rate which defined by Date

[#1]

Hi guys,

I have two tables as below, one is Rate and the other is Sales:

What I want is to calculate the Amt according to the VL_Rate, but the Rate is defined by the Active_Date and Expiry_Date,

in another word, the sales Date must between them.

For example, if I want to see the AUD Market Amount of USD, I should calculate as below:

Market     Date               Amount

AUD     2012-10-19      59802/1.03

AUD     2013-08-12      62321/1.04

AUD     2013-11-12      60874/1.04

But I do not know how to write the expression for this, anyone can help me?

Thank you very much.

Finally, it should be like this:

[#2]

Hi

PFA Hope this will helps you

[#3]

Hi Nirmal,

Thank you for you reply, but it seems not correct,

The first record:

Amt_According to Date of AUD 2012-10-19 should be:59802/1.03=5860.19, not 28889.85

[#4]

Hi

PFA now

[#5]

Hi Nirmal,

Thank you much, but sorry for my mistake, I want to clarify what I want to get, first we must select the field To_Rate,for example we select USD,that means we want to see the amt of US dollar. In this case, if USD is selected, the exchange rate should be 1.03 for date 2012-10-19 and 1.04 for date 2013-08-12, then the amt for the date 2012-10-19 should be amt/1.03 and for  date 2013-08-12 should be amt/1.04.

The result should be:

Market      Date             Amt      Amt_According to Date

AUD      2012-10-19      59802      59802/1.03

             2013-08-12      62321      62321/1.04

             2013-11-12      60874      60874/1.04