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:
- Transaction Sales Revenue = The revenue in the originating currency
- Home Sales Revenue = The revenue represented in the local division currency (home currency)
- Consolidated Sales Revenue in CAD = The sales amounts all consolidated into Canadian currency
- Consolidated Sales Revenue in USD = The sales amounts all consolidated into United States currency
Here is what I've attempted to code in order to satisfy the above requirements:
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)) < 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 <> '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
Here's the sample data from some of the tables: Sample date from Main Invoice Header, Home Currency, and Exchange Rates tables
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
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.
If anyone can get me back on track, that would be great!
