Skip to Content
0

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

Jan 23, 2017 at 04:37 PM

37

avatar image

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 left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
DIEGO LOTHER Jan 23, 2017 at 05:20 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Roy Bright Jan 24, 2017 at 04:11 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded