Currency conversion calculation

Question

Post reply

Currency conversion calculation

yingchai

SSCommitted

Points: 1669
More actions
September 10, 2011 at 11:16 pm

#244629

Hi,
I have been cracking my head on this and need some help on currency conversion calculations from two tables here.
Fact table
ACCOUNT | UNIT | CURRENCY | AMOUNT | PERIOD | YEAR | DATASOURCE
-----------------------------------------------------------------------
1001 | SWH | SGD | 10.00 | 06 | 2011 | Source
1001 | SWH | USD | 8.00 | 06 | 2011 | Translated
1001 | SWH | USD | 8.00 | 06 | 2011 | Source
1001 | SWH | MYR | 24.00 | 06 | 2011 | Translated
1001 | SWH | USD | 15.00 | 06 | 2011 | Source
1001 | SWH | SGD | 80.00 | 06 | 2011 | Translated
Exchange Rate table
FOREIGN_CURR | LOCAL_CURR | PERIOD | YEAR | VALUE
------------------------------------------------------
MYR | MYR | 06 | 2011 | 1.0000
SGD | MYR | 06 | 2011 | 2.4195
USD | MYR | 06 | 2011 | 3.0260
Based on the Fact table above, I need to convert the amount from foreign currency to local currency only for those records which DATASOURCE = Translated. The exchange rate value is stored in a rate table. Below is the desired output:
Result view
ACCOUNT | UNIT | CURRENCY | AMOUNT | PERIOD | YEAR | DATASOURCE
-----------------------------------------------------------------------
1001 | SWH | SGD | 10.00 | 06 | 2011 | Source
1001 | SWH | MYR | 24.21 | 06 | 2011 | Translated //comment: 8.00*3.0260
1001 | SWH | USD | 8.00 | 06 | 2011 | Source
1001 | SWH | MYR | 24.00 | 06 | 2011 | Translated //comment: 24.00*1.0000
1001 | SWH | USD | 15.00 | 06 | 2011 | Source
1001 | SWH | MYR | 193.56 | 06 | 2011 | Translated //comment: 80.00*2.4195
Can anyone help me on how to calculate the Result view? Your kind help is greatly appreciated.
THANKS!
Edit: Sorry for the double post due to my slow internet connection 🙁

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

This?

SELECT * , NEWRATE = t1.AMOUNT * t2.VALUE

from [Fact table] t1

inner join [Exchange Rate table] t2

on t1.CURRENCY = t2.FOREIGN_CURR

and t1.DATASOURCE = 'Translated'

yingchai SSCommitted Points: 1669 More actions · Answer 2

ColdCoffee (9/11/2011)
This?
SELECT * , NEWRATE = t1.AMOUNT * t2.VALUE
from [Fact table] t1
inner join [Exchange Rate table] t2
on t1.CURRENCY = t2.FOREIGN_CURR
and t1.DATASOURCE = 'Translated'

Hi ColdCoffee,

EDIT: I had tried your query but it returned duplicate rows. Results show something like this...

Result view

-----------------------------------------------------------------------

1001 | SWH | SGD | 10.00 | 10.00 | 06 | 2011 | Source

1001 | SWH | USD | 8.00 | 8.00 | 06 | 2011 | Translated

1001 | SWH | USD | 8.00 | 24.21 | 06 | 2011 | Translated

1001 | SWH | USD | 8.00 | 8.00 | 06 | 2011 | Source

1001 | SWH | MYR | 24.00 | 24.00 | 06 | 2011 | Translated

1001 | SWH | USD | 15.00 | 15.00 | 06 | 2011 | Source

1001 | SWH | SGD | 80.00 | 80.00 | 06 | 2011 | Translated

1001 | SWH | SGD | 80.00 | 193.56 | 06 | 2011 | Translated

It does not update the currency in the CURRENCY column from foreign currency to local currency sign.

Please assist. Thx.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi

yingchai (9/11/2011)
It does not update the currency in the CURRENCY column from foreign currency to local currency sign.

Huh? ColdCoffee's is correct in my opinion. The result returns all data of your facts table and all data of the matching exchange rate table. Please check your results and change the source column of your "Currency" result column.

Greets

Flo

yingchai SSCommitted Points: 1669 More actions · Answer 4

Florian Reischl (9/11/2011)
Hi
yingchai (9/11/2011)
It does not update the currency in the CURRENCY column from foreign currency to local currency sign.
Huh? ColdCoffee's is correct in my opinion. The result returns all data of your facts table and all data of the matching exchange rate table. Please check your results and change the source column of your "Currency" result column.
Greets
Flo

Hi,

Edit: Yes, you are right. My result will show like this: