December 17, 2010 at 6:50 am
Hi
I have a transaction table (Invoices) and a CurrencyTable. In Currency table Currency Exchnage Rates are available with Exchange Date.
In transaction table there two columns (InvoicePrice, InvoiceCurrency).
InvoicePrice store InvoiceAmt and InvoiceCurrency column store currency in which Invoice generated. I need to convert from InvoiceCurrency to USD (US Dollar) and for that I have use Currency Table.
There are mulpitle Exchnage Rates are available in Currency Table with respective Exchange Date.
I have to pick up the latest Exchnage Rate according to Invoice Date.
For Example see below
Currency Table
ExchnageRate, ExchnageDate,FromCurr, ToCurr
3.45, 1-Jan-2010, EUR, USD
3.54, 15-Jan-2010, EUR, USD
Invoices
1st Transaction
-----------------
InvoiceDate,InvoiceCurrency,InvoiceAmt
12-Jan-2010,EUR, 500,
This Transaction should use the Exchnage Rate of Date 1-Jan-2010
2nd Transaction
---------------------
InvoiceDate,InvoiceCurrencyInvoiceAmt,
18-Jan-2010,EUR, 900,
This Transaction should use the Exchnage Rate of Date 15-Jan-2010
Can anyone suggest what SQL query I need write to achive above logic.
December 17, 2010 at 1:36 pm
Can you post the table definitions so that we can give you a working query instead of pseudocode?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply