/*

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