Currency conversion calculation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Currency conversion calculation

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2011-09-11 : 01:13:18

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-11 : 11:46:09

[code]SELECT f.ACCOUNT , f.UNIT , f.CURRENCY ,
f.AMOUNT * CASE WHEN f.DATASOURCE='Translated' THEN er.VALUE ELSE 1 END AS AMOUNT,
f.PERIOD , f.YEAR,
f.DATASOURCE + CASE WHEN f.DATASOURCE='Translated' THEN '//comment: ' + CAST(f.AMOUNT AS varchar(10)) + '*' + CAST(er.VALUE AS varchar(10)) ELSE '' END
FROM Fact f
LEFT JOIN ExchangeRate er
ON er.FOREIGN_CURR = f.CURRENCY
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources