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: 39971 More actions · Answer 1

ColdCoffee

SSC-Dedicated

Points: 39971

September 11, 2011 at 1:24 am

#1379662

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: 37299 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

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

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: