Skip to Content
1

# Convert Exchange rate in Query

Nov 28, 2016 at 09:15 AM

62

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

* Please Login or Register to Answer, Follow or Comment.

### 2 Answers

Best Answer
Johan Hakkesteegt Nov 28, 2016 at 11:32 AM
0

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

Share
Roy Bright Dec 05, 2016 at 11:08 AM
0

Thank you so much, Johan. This works superb.

Regards

Roy

Share