Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    8

    Unanswered: Currency conversion calculation

    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. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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_
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2010
    Posts
    8
    Quote Originally Posted by Wim View Post
    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.
    Please assist. Thx.
    Last edited by yingchai; 09-11-11 at 13:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •