title: Handling currency conversion in SQL Server T-SQL
body: <p>I have attempted to build an SQL script in MS SQL Server T-SQL to split a Sales Invoice Revenue measure into 4 measures representing the following:</p>
  
  <ul>
  <li>Transaction Sales Revenue = The revenue in the originating currency</li>
  <li>Home Sales Revenue = The revenue represented in
  the local division currency (home currency)</li>
  <li>Consolidated Sales Revenue in CAD = The sales amounts all consolidated into 
  Canadian currency</li>
  <li>Consolidated Sales Revenue in USD = The sales amounts all consolidated into 
  United States currency</li>
  </ul>
  
  <p>Here is what I've attempted to code in order to satisfy the above requirements:</p>
  
  <pre><code>    Main_InvoiceTable AS
  (   --This is the root query, retrieving rows from OrderHeader Table
      select  frhkrg.* ,
              convert(money, isnull(x.rate_exchange, 1))  as TMP_ExchangeRate,
              coalesce(source_currency,'NA') as TMP_SourceTransactionCurrencyType,
              coalesce(target_currency,'NA') as TMP_TargetTransactionCurrencyType
      from    frhkrg
                  LEFT OUTER JOIN rates x --Note, the rate here is to convert FROM USD to CAD (USD * RATE = CAD)
                      on CONVERT(varchar(6), frhkrg.fakdat, 112) = convert(varchar(4),year(x.date_l)) + 
  case
      when convert(varchar(2), month(x.date_l)) &lt; 10 then '0' +  convert(varchar(2), month(x.date_l))
      else  convert(varchar(2), month(x.date_l)) end
  ),
  
  ,Home_Currency as 
  (
      SELECT  Entity.[Legal Entity Code] as EntityCode,
              Entity.[Legal Entity Desc] as EntityName,
              Entity.[Local Currency] as EntityCurrency
      from    dbo.tmpBU Entity
      group by
              Entity.[Legal Entity Code],
              Entity.[Legal Entity Desc],
              Entity.[Local Currency]
  ),
  
  Main_Query as
  (
  select
   Main_InvoiceTable.faknr    as [Invoice Num]
  ,Main_InvoiceTable.fakdat as    [Invoice Date]
  ,Main_InvoiceTable.ordernr as [Sales Order Number]
  ,InvoiceDetail.esr_aantal as [Sales Revenue]
  
      ,Home_Currency.EntityCurrency as [Home Currency Cd]
      ,Main_InvoiceTable.TMP_SourceTransactionCurrencyType  as [Source Transaction Currency Cd]
      ,Main_InvoiceTable.TMP_TargetTransactionCurrencyType  as [Target Transaction Currency Cd]
      ,Main_InvoiceTable.TMP_ExchangeRate as [US/CDN Exchange Rate]
  
  --If the transaction currency Code is different than the Home Currency Code then do the following conditional logic 
              ,case   when Home_Currency.EntityCurrency = 'USD' AND Main_InvoiceTable.TMP_SourceTransactionCurrencyType = 'USD'
                          then 1 --SourceCurrency and Homecurrency are the same, no need to convert.
  
                      when Home_Currency.EntityCurrency = 'USD' AND Main_InvoiceTable.TMP_SourceTransactionCurrencyType= 'CAD'
                          then 1 / Main_InvoiceTable.TMP_ExchangeRate --convert from CAD to USD, use the reverse of the conversion rate
  
                      when Home_Currency.EntityCurrency = 'CAD' AND Main_InvoiceTable.TMP_SourceTransactionCurrencyType = 'USD'
                          then Main_InvoiceTable.TMP_ExchangeRate --if we need to convert from USD to CAD, use the "direct" conversion rate
  
                      when Home_Currency.EntityCurrency = 'CAD' AND Main_InvoiceTable.TMP_SourceTransactionCurrencyType = 'CAD'
                          then 1 --SourceCurrency and Homecurrency are the same, no need to convert.
                      else 1
                  end as TMP_Convert_To_Entity_Currency,
  
              case    when Main_InvoiceTable.TMP_SourceTransactionCurrencyType &lt;&gt; 'CAD' 
                      then Main_InvoiceTable.TMP_ExchangeRate --to convert FROM USD to CAD
                      else 1 --consolidated Currency should be either CAD or in USD (currently, the logic dictates that consolidated is always in CAD)
                  end as TMP_Convert_To_Consolidated_Currency
  
              from    Main_InvoiceTable
                      join    Home_Currency on Main_InvoiceTable.[Division Code] = Home_Currency.EntityCode
                      join    dbo.frhsrg InvoiceDetail on Main_InvoiceTable.faknr = InvoiceDetail.faknr
  )
  
  select
   [Invoice Num]
  ,[Invoice Date]
  ,[Sales Order Number]
  ,[Sales Revenue] as [Transaction Sales Revenue]
  ,[Sales Revenue] * TMP_Convert_To_Entity_Currency as [Entity Sales Revenue]
  ,[Sales Revenue] * TMP_Convert_To_Consolidated_Currency as [Consolidated Sales Revenue]
  
  from Main_Query
  
  group by 
   [Invoice Num]
  ,[Invoice Date]
  ,[Sales Order Number]
  ,[Sales Revenue]
  ,TMP_Convert_To_Entity_Currency
  ,TMP_Convert_To_Consolidated_Currency
  </code></pre>
  
  <p>Here's the sample data from some of the tables:
  <a href="https://i.stack.imgur.com/N9djf.jpg" rel="nofollow noreferrer">Sample date from Main Invoice Header, Home Currency, and Exchange Rates tables</a></p>
  
  <pre><code>Main Invoice Table Header Query
  -------------------------------
  Invoice Num | Invoice Date  | Order Num | Order Date   | Division Code | Warehouse | Exchange Rate | Source Currency | Target Currency
  5000001     | July-20-18    | 50052     | June-18-18   | 859           | _MTL      | 1.3017        | USD             | CAD
  5000001     | July-20-18    | 50052     | June-18-18   | 859           | _MTL      | 1.5239        | EUR             | CAD
  6000001     | October-30-18 | 60033     | August-22-18 | 952           | _ARIZONA  | 1.0000        | USD             | USD
  6000001     | October-30-18 | 60033     | August-22-18 | 952           | _ARIZONA  | 1.1502        | EUR             | USD
  
  Home Currency Query
  -------------------                             
  Division Code | Division Name                    | Home Currency                        
  199           | T Reliable Manufacturing Inc.    | USD                      
  200           | C Verification Technologies Inc. | USD                      
  330           | T France  SARL                   | EUR                      
  400           | LauchWorks  LLC                  | CAD  
  859           | C Industries (2007) ULC -MTL     | CAD                      
  889           | C (2007) ULC -VCR                | CAD                      
  952           | DDL  LLC                         | USD                      
  999           | N Petrochemicals                 | CAD                      
  
  Exchange Rate Table
  -------------------                         
  source_currency | target_currency | date_l          | rate_exchange | Division              
  CAD             | EUR             | September-02-12 | 1             | 859               
  CAD             | GBP             | September-02-12 | 1             | 859               
  CAD             | USD             | August-30-12    | 1             | 859               
  EUR             | ATS             | January-01-99   | 13.7603       | NULL              
  EUR             | BEF             | January-01-99   | 40.3399       | NULL              
  EUR             | CAD             | May-31-07       | 1.4536        | 859               
  EUR             | CAD             | June-08-07      | 1.4173        | 859               
  USD             | CAD             | August-31-18    | 1.3055        | 859               
  USD             | CAD             | September-30-18 | 1.2945        | 859           
  </code></pre>
  
  <p>I believe I am on the right track.  However, I am not confident that my script fulfills all requirements particularly when it comes to obtaining the Consolidated CAD and Consolidated USD sales figures.</p>
  
  <p>If anyone can get me back on track, that would be great!</p>