### Query calculation not taking T1.Price from currency calculation in query

Hi guys - I have the following query that i wrote but have just started to realise that, even though the query works and that the currency conversion does what it is supposed to, the final outcome of GP appears that, the gross profit calculation is using the T1.Price from the product table and NOT from the final outcome within the query.

Can someone advise where I have gone wrong with this as it is returning the wrong GP%

Thanks
Roy Bright

SELECT T1.[CardCode] AS 'BP Code'
, T1.[ItemCode] AS 'Item No.'
, CASE
WHEN T2.[Currency] = 'EUR' THEN T1.[Price] * 1.19
WHEN T2.[Currency] = 'USD' THEN T1.[Price] * 1.26
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 %' ***THIS PART IS NOT CALCULATING AGAINST THE -- ELSE T1.[Price] END AS 'Special Price' -- PART***

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

Jan 23, 2017 at 05:20 PM

Hi Roy,

If I understood correctly your situation, it is necessary that you replicates your logic of 'Special Price' in your calculation of gross profit %, like this:

```SELECT
T1.[CardCode] AS 'BP Code'
, T1.[ItemCode] AS 'Item No.'
, CASE WHEN T2.[Currency] = 'EUR' THEN T1.[Price] * 1.19 WHEN T2.[Currency] = 'USD' THEN T1.[Price] * 1.26 WHEN T2.[Currency] = 'GBP' THEN T1.[Price] * 1 ELSE T1.[Price] END AS 'Special Price'
, T0.[LastPurPrc] AS 'Last Purchase Price'
, T2.[Currency]
, ((CASE WHEN T2.[Currency] = 'EUR' THEN T1.[Price] * 1.19 WHEN T2.[Currency] = 'USD' THEN T1.[Price] * 1.26 WHEN T2.[Currency] = 'GBP' THEN T1.[Price] * 1 ELSE T1.[Price] END) - T0.[LastPurPrc]) /
(CASE WHEN T2.[Currency] = 'EUR' THEN T1.[Price] * 1.19 WHEN T2.[Currency] = 'USD' THEN T1.[Price] * 1.26 WHEN T2.[Currency] = 'GBP' THEN T1.[Price] * 1 ELSE T1.[Price] END) * 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]'
```

Or you can try do something like this:

```SELECT
*,
((T0.[Special Price] - T0.[Last Purchase Price]) / T0.[Special Price]) * 100 AS 'Gross Profit %'
FROM
(SELECT
T1.[CardCode] AS 'BP Code'
, T1.[ItemCode] AS 'Item No.'
, CASE WHEN T2.[Currency] = 'EUR' THEN T1.[Price] * 1.19 WHEN T2.[Currency] = 'USD' THEN T1.[Price] * 1.26 WHEN T2.[Currency] = 'GBP' THEN T1.[Price] * 1 ELSE T1.[Price] END AS 'Special Price'
, T0.[LastPurPrc] AS 'Last Purchase Price'
, T2.[Currency]
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]') T0
```

Hope it helps.

Kind Regards,

Diego Lother