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

[former_member239716]

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

[former_member185682 · Accepted solution]

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

[former_member239716]

Hi Diego - thanks for this buddy - I have kinda realised that my calculations are incorrect and that i am affecting teh wrong string for teh conversion - the top one is perfect and I will adjust once I speak with my colleague, the Financial Director as to hwo we convert the currency.

Superb work as always bro 🙂

Thank you

Roy