December 17, 2010 at 7:24 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 9:54 am
If I were doing this, I would modify the Currency table to include [ExchangeStartDate] and [ExchangeEndDate] fields. Then I would use a range in the join clause of the query:
FROM Invoices inv
INNER JOIN Currency cur
ON inv.InvoiceDate BETWEEN cur.ExchangeStartDate AND cur.ExchangeEndDate
I'm sure there are several other approaches to this as well. This is really more of a T-SQL question.
December 17, 2010 at 2:37 pm
I think Ed is on the right track, doing this at the DB instead of in SSIS is a better approach. SSIS does not offer a between type lookup, the records much match exactly. SQL would do a much better job and much faster on this kind of query.
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply