Skip to Content
1

Convert Exchange rate in Query

Nov 28, 2016 at 09:15 AM

54

avatar image

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 left characters exceeded
* 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
10 |10000 characters needed characters left characters exceeded
Roy Bright Dec 05, 2016 at 11:08 AM
0

Thank you so much, Johan. This works superb.

Regards

Roy

Share
10 |10000 characters needed characters left characters exceeded