1. Registered User
Join Date
Apr 2010
Posts
8

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!

2. Registered User
Join Date
Nov 2004
Posts
1,428
Try this:
Code:
```SELECT F.ACCOUNT,
F.UNIT,
F.CURRENCY,
CAST(CASE WHEN F.DATASOURCE = 'Translated'
THEN ROUND(F.AMOUNT * E.VALUE, 2)
ELSE F.AMOUNT
END AS DEC(10, 2)) AS AMOUNT,
F.PERIOD,
F.YEAR_,
F.DATASOURCE
FROM #Fact as F
INNER JOIN #ExchangeRate as E ON
F.CURRENCY = E.FOREIGN_CURR AND
F.PERIOD = E.PERIOD AND
F.YEAR_ = E.YEAR_```

3. Registered User
Join Date
Apr 2010
Posts
8
Originally Posted by Wim
Try this:
Code:
```SELECT F.ACCOUNT,
F.UNIT,
F.CURRENCY,
CAST(CASE WHEN F.DATASOURCE = 'Translated'
THEN ROUND(F.AMOUNT * E.VALUE, 2)
ELSE F.AMOUNT
END AS DEC(10, 2)) AS AMOUNT,
F.PERIOD,
F.YEAR_,
F.DATASOURCE
FROM #Fact as F
INNER JOIN #ExchangeRate as E ON
F.CURRENCY = E.FOREIGN_CURR AND
F.PERIOD = E.PERIOD AND
F.YEAR_ = E.YEAR_```
Hi Wim,
EDIT: I had tried your query but it returned duplicate rows. Results show something like this...

Result view
ACCOUNT | UNIT | CURRENCY | AMOUNT | AMOUNT | PERIOD | YEAR | DATASOURCE
-----------------------------------------------------------------------
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 | 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.