Skip to Content

Convert Exchange rate in Query

Hey guys - is there a way to add a line to the below query, that when it sees the customer's currency is EUR it divides the T1.Price column by 1.12 (or whatever the x-rate that I set)?

SELECT T1.[CardCode] AS 'BP Code'
, T1.[ItemCode] AS 'Item No.'
, T1.[Price] AS 'Special Price'
, T0.[LastPurPrc] AS 'Last Purchase Price'
, T2.[Currency]
, (T1.[Price] - T0.[LastPurPrc]) / (T1.[Price]) * 100 AS 'Gross Profit %'
FROM [dbo].[OITM] T0
INNER JOIN [dbo].[OSPP] T1 ON T1.[ItemCode] = T0.[ItemCode]
INNER JOIN [dbo].[OCRD] T2 ON T2.[CardCode] = T1.[CardCode]
WHERE T1.CardCode = '[%0]'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 28, 2016 at 11:32 AM

    Hi Roy,

    This is possible. Please take a look at this example, but beware that you would have to (and can) use this same mechanism everywhere you are using the T1.[Price] field:

    SELECT T1.[CardCode] AS 'BP Code'
    , T1.[ItemCode] AS 'Item No.'
    , CASE 
       WHEN T2.[Currency] = 'EUR' THEN T1.[Price] * 1.12
       WHEN T2.[Currency] = 'USD' THEN T1.[Price] * 1.08
       WHEN T2.[Currency] = 'GBP' THEN T1.[Price] * 1
       /* etc */
       ELSE T1.[Price]
      END AS 'Special Price'
    , T0.[LastPurPrc] AS 'Last Purchase Price' 
    , T2.[Currency]
    , (T1.[Price] - T0.[LastPurPrc]) / (T1.[Price]) * 100 AS 'Gross Profit %'
    FROM [dbo].[OITM] T0 
    INNER JOIN [dbo].[OSPP] T1 ON T1.[ItemCode] = T0.[ItemCode]
    INNER JOIN [dbo].[OCRD] T2 ON T2.[CardCode] = T1.[CardCode]
    WHERE T1.CardCode = '[%0]'

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 05, 2016 at 11:08 AM

    Thank you so much, Johan. This works superb.

    Regards

    Roy

    Add comment
    10|10000 characters needed characters exceeded