SAP Exchange rate

/*
Description: SAP Exchange rate

The exchange rate is used as:
CurrencyFrom = ExchangeRate * CurrencyTo

Data can be found compared with SAP via:
> T-Code: SPRO
> SAP Refenrece IMG
> Customizing Implementation Guide > SAP NetWeaver > General Settings > Currencies > …

The conversion logic is:
Rate(For Indirect Quotation) * RatioFrom * CurrencyFrom =
Rate(For Direct Quotation) * RatioTo * CurrencyTo
Rate>0 it means direct quotation, while rate<0 means indirect quotation.
Thus the final output rate is calculated as:
ExchangeRate = (RatioTo / RatioFrom) * Rate , if Rate>0
(RatioTo / RatioFrom) /(-Rate), if Rate<0

RatioFrom/To are normally 1, but are elsewise used as scale factors 10,100,100 etc.
It is allowed that RatioFrom=0 and RatioTo=0 (at the same time):
It means that we must look up the ratios in the ‘conversion factors table’ instead.
The conversion factors table has ValidFrom date, and for each pick the largest ValidFrom
that are also smaller than the date for the rate in question.

The ‘inverted’ SAP date format is translated as
Date(yyyymmdd) = 99999999-GDATU
Note that, if we operate directly on GDATU, because of the minus-sign we must interchange ASC with DESC, and > with <.

The IsFaulty flag is set for various missing/wrong/inconsistenst values, see comments in code.

The IsOutlier3Sigma flag can be used to highlight possible errors (only an indication).
The flag is set if the value lies more than 3 standard deviations away from the mean.
(For normal distributions 99,7% of the data lies within 3 sigma.)

*/

DECLARE @UnknownDate DATE = ‘9999-30-01’;

WITH t1 AS (
SELECT
rate.KURST AS MapSAPKURST — ExchangeRateType
, rate.FCURR AS MapSAPFCURR — From Currency
, rate.TCURR AS MapSAPTCURR — To Currency
, ISNULL(TRY_CAST(TRY_CAST(99999999-TRY_CAST(rate.GDATU AS INT) AS VARCHAR(MAX)) AS DATE), @UnknownDate) AS TTimeStamp
, CASE WHEN rate.UKURS < 0 THEN 1/NULLIF(CAST(-rate.UKURS AS FLOAT), 0) ELSE rate.UKURS END AS UKURS
, CASE WHEN rate.FFACT = 0 AND rate.FFACT = 0 THEN factor.FFACT ELSE rate.FFACT END AS FFACT
, CASE WHEN rate.FFACT = 0 AND rate.TFACT = 0 THEN factor.TFACT ELSE rate.TFACT END AS TFACT
, CASE WHEN
TRY_CAST(TRY_CAST(99999999-TRY_CAST(rate.GDATU AS INT) AS VARCHAR(MAX)) AS DATE) IS NULL — invalid date
OR (rate.FFACT=0 AND rate.TFACT=0 AND factor.KURST IS NULL) — lookup factor missing in left join
OR (rate.FFACT=0 AND rate.TFACT<>0) — only both factors allowed 0 simultaneously
OR (rate.FFACT<>0 AND rate.TFACT=0) — only both factors allowed 0 simultaneously
OR rate.UKURS=0 OR rate.UKURS IS NULL — missing rate or divide by zero
— OR (rate.FFACT<>0 AND rate.TFACT<>0 AND (rate.FFACT<>factor.FFACT OR rate.TFACT<>factor.TFACT) ) –factors inconsistent (not faulty, only a ‘warning’)
THEN 1 ELSE 0 END AS IsFaulty_pre
/* For debugging: */
–, rate.GDATU AS GDATU_rate
–, rate.UKURS AS UKURS_rate
–, rate.FFACT AS FFACT_rate
–, rate.TFACT AS TFACT_rate
–, factor.GDATU AS GDATE_factor
–, factor.FFACT AS FFACT_factor
–, factor.TFACT AS TFACT_Factor
–, ISNULL(TRY_CAST(TRY_CAST(99999999-TRY_CAST(factor.GDATU AS INT) AS VARCHAR(MAX)) AS DATE), @UnknownDate) AS DateValidFrom_factors
–, factor.ABWCT
–, factor.ABWGA
FROM TCURR AS rate
OUTER APPLY (
SELECT TOP 1 *
FROM TCURF
WHERE DWARXIsCurrent = 1
AND DWARXIsDeleted = 0
AND KURST = rate.KURST
AND FCURR = rate.FCURR
AND TCURR = rate.TCURR
AND GDATU >= rate.GDATU /* Corresponds to (99999999-GDATU) <= (99999999-rate.GDATU) !*/
ORDER BY GDATU ASC /* Corresponds to (99999999-GDATU) DESC !*/
) AS factor
WHERE rate.DWARXIsCurrent = 1
)
, t2 AS (
SELECT *
, ISNULL(t1.UKURS * t1.TFACT / NULLIF(t1.FFACT, 0), 0) AS ExchangeRate
, CASE WHEN t1.IsFaulty_pre = 1
OR t1.UKURS <= 0 OR t1.UKURS IS NULL
OR t1.FFACT <= 0 OR t1.FFACT IS NULL
OR t1.TFACT <= 0 OR t1.TFACT IS NULL
THEN 1 ELSE 0 END AS IsFaulty
FROM t1
)
, t3 AS (
SELECT *
, AVG(t2.ExchangeRate) OVER (PARTITION BY MapSAPKURST, MapSAPFCURR, MapSAPTCURR) AS Mean
, STDEV(t2.ExchangeRate) OVER (PARTITION BY MapSAPKURST, MapSAPFCURR, MapSAPTCURR) AS Sigma
FROM t2
)
SELECT
t3.MapSAPKURST
, t3.MapSAPFCURR
, t3.MapSAPTCURR
, t3.TTimeStamp
, CONVERT(FLOAT, t3.ExchangeRate) AS ExchangeRate
, CONVERT(BIT, t3.IsFaulty) AS IsFaulty
, CONVERT(BIT, CASE WHEN ABS(t3.ExchangeRate-Mean) > 3 * t3.Sigma THEN 1 ELSE 0 END) AS IsOutlier3Sigma
, t3.IsDeleted
/* For debugging: */
–, GDATU_rate
–, UKURS_rate
–, FFACT_rate
–, TFACT_rate
–, GDATE_factor
–, FFACT_factor
–, TFACT_Factor
–, DateValidFrom_factors
FROM t3