0

I have a exchange rate table like below

FromCurrency ToCurrency ValidFrom ExchangeRate USD ZAR 2012-01-05 00:00:00.000 7.7260000000 USD ZAR 2012-01-04 00:00:00.000 7.6740000000 USD ZAR 2012-01-03 00:00:00.000 7.4601000000 USD ZAR 2012-01-02 00:00:00.000 7.7600000000 USD ZAR 2012-01-01 00:00:00.000 8.0945000000 EUR NOK 2012-01-05 00:00:00.000 7.5881000000 EUR NOK 2012-01-04 00:00:00.000 7.5974000000 EUR NOK 2012-01-03 00:00:00.000 7.4494000000 EUR NOK 2012-01-02 00:00:00.000 7.6606000000 EUR NOK 2012-01-01 00:00:00.000 7.7740000000 USD AED 2012-01-05 00:00:00.000 3.6731000000 USD AED 2012-01-04 00:00:00.000 3.6731000000

My main table is 

Transaction Date Amount Currency FromCurrency 13971 5/27/2011 8000 USD USD 13971 7/31/2011 -6809.4 ZAR USD 13971 8/30/2011 -425.59 ZAR USD 13971 9/27/2011 -6809.4 ZAR USD 67467 11/8/2011 5000 GBP GBP 67467 12/21/2011 -5195.06 ZAR GBP 67467 1/30/2012 -5195.06 ZAR GBP 81181 4/15/2011 6000 USD USD 81181 6/28/2011 -5159.03 ZAR USD 82418 10/21/2011 5000 EUR EUR 82418 1/27/2012 -3919.97 NOK EUR

Above is sample data of table, my actual table has millions of records. I have to multiply amount with exchange rate column (from exchange rate table) and update the corresponding FromCurrency (from exchange rate table) with currency column in my table.

I have tried many ways but couldn't find the solution. The tricky part here is there is not matching date for my table in exchange rate table.

Exchange rate should be applied like this. If my transaction date is Feb-20-2012 and Currency is ZAR and FromCurrency is USD then I have to pick the below row from exchange rate table

USD ZAR 2012-01-02 00:00:00.000 7.7600000000
Improve this question
2
  • what would be your output table can u show me? Commented Aug 25, 2012 at 11:47
  • @AnandPhadke myoutput table is same as my main table. I have to update amount and currecy column. Commented Aug 25, 2012 at 11:51

3 Answers 3

Reset to default
1 
update transactions set FromCurrencyAmount = transactions.amount * (select top 1 exchangerate from exchangerates where validfrom <= transactions.date and transactions.fromcurrency = exchangerates.fromcurrency and transactions.currency = exchangerates.tocurrency order by validfrom desc)

To select

select transactions.*, (select top 1 exchangerate from exchangerates where validfrom <= transactions.date and transactions.fromcurrency = exchangerates.fromcurrency and transactions.currency = exchangerates.tocurrency order by validfrom desc) from transactions
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Can you please give the select version of this query. I want to select the exchange rates and see before actually updating my main table. Many thank for the answer.
@poduluska Thanks a lot, really I couldn’t convert that statement to select statement. so much to learn in SQL !
@poduluska I noticed that this query is getting me top exchange rate form the the selected bucket. For USD to ZAR, If I have 1-Mar-2012, 1-May-2012, 1-Jun-2012 exchange rates. Query is getting me 1-Jun-2012 exchange rate even if my transaction date is 1-Apr-2012.
It should get 1-Mar-2012 exchange rate for 11-Apr-2012 transaction and 1-May-2012 exchange rate for 21-May-2012 transaction. But this query is getting only 1-Jun-2012 exchange rate(top in the bucket) exchange rate for both the transactions.
@Nani You are either storing your dates oddly, or you have mistransposed the query.
|
1

Another option is :

select * from transact t1 join exchRate e1 on t1.FromCurrency = e1.FromCurrency and t1.ToCurrency = e1.ToCurrency and t1.tdate > e1.ValidFrom and not exists (select 1 from exchRate where ValidFrom < t1.tdate and ValidFrom > e1.ValidFrom)
Improve this answer

3 Comments

thank you for the answer. I am having same problem as I mentioned below with this approch too. For USD to ZAR, If I have 1-Mar-2012, 1-May-2012, 1-Jun-2012 exchange rates. Query is getting me 1-Jun-2012 exchange rate even if my transaction date is 11-Apr-2012 or 21-May-2012. It should get 1-Mar-2012 exchange rate for 11-Apr-2012 transaction and 1-May-2012 exchange rate for 21-May-2012 transaction.
I think you are confused with date format (e.g 01/03/2012 may be 1-Mar-2012 or 3-Jan-2012 )
you are absolutely correct. Exchange rate table date was in UK format and my main table date column is in us format. sorry, I rectifed the mistake. thank you.
0

I hope understand

 SELECT T3.*, T4.DATEMAX, T4.VALUESELECTED FROM TRANSACTIONS AS T3 INNER JOIN (SELECT T.CURRENCY, T.FROMCURRENCY, T.DATEMAX, T2.EXCHANGERATE AS VALUESELECTED FROM (SELECT CURRENCY, FROMCURRENCY, MAX(ValidFrom) AS DATEMAX FROM ExChangeRate WHERE ValidFrom <= T3.DATE GROUP BY Currency, FromCurrency) AS T INNER JOIN ExChangeRate AS T2 ON T.CURRENCY = T2.CURRENCY AND T.FROMCURRENCY = T2.FROMCURRENCY AND T.DATEMAX = T2.VALIDFROM) AS T4 ON T4.CURRENCY = T3.CURRENCY AND T4.FROMCURRENCY = T3.FROMCURRENCY;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.