### 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]'

10|10000 characters needed characters exceeded

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