Skip to Content
0

GP Percentage Help

Jan 04, 2017 at 04:51 PM

42

avatar image

I am trying to get the GP percentage to match up with the sales analysis but it always seem to be a few percent out when multiple sales orders are involved, do you have any suggestions as to what is wrong with the query below?

SELECT T0.[CardCode] as 'Customer ID', T0.[CardName] as 'Customer', SUM(T0.[DocTotal]-T0.[VatSum]) as 'Total Value £',

AVG(CASE
WHEN (T0.DocTotal) <> 0 then
(T0.[GrosProfit]/(T0.[DocTotal]-T0.[VatSum])) * 100
ELSE 0
END) AS 'GP %',

COUNT(T0.[DocNum]) as 'Number of Orders'

FROM ORDR T0 INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
WHERE T0.[DocDate] = convert(date, getdate()) and T0.[CANCELED] = 'n'
GROUP BY T0.[CardCode], T0.[CardName]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
DIEGO LOTHER Jan 04, 2017 at 06:08 PM
1

Hi Scott,

Try this:

SELECT 
	T0.[CardCode] AS 'Customer ID', 
	T0.[CardName] AS 'Customer', 
	SUM(T0.[GrosProfit]) AS 'Gross Profit',
	SUM(T0.[DocTotal]-T0.[VatSum]) AS 'Total Value £', 
	(SUM(CASE WHEN (T0.DocTotal) <> 0 THEN T0.[GrosProfit] ELSE 0 END) / 
	 SUM(CASE WHEN T0.DocTotal <> 0 THEN T0.[DocTotal]-T0.[VatSum] ELSE 0 END)) * 100 AS  'GP %', 
	COUNT(T0.[DocNum]) AS 'Number of Orders' 
FROM 
	ORDR T0 
	INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] 
WHERE 
	T0.[DocDate] = convert(date, getdate()) 
	AND T0.[CANCELED] = 'N' 
GROUP BY 
	T0.[CardCode], T0.[CardName]

Hope it helps.

Kind Regards,

Diego Lother

Share
10 |10000 characters needed characters left characters exceeded
Scott Cox Jan 06, 2017 at 05:06 PM
0

Hi Diego,

Thank you very much for your help, the amended code works perfectly.

Regards

Scott

Share
10 |10000 characters needed characters left characters exceeded
Scott Cox Jan 10, 2017 at 09:41 AM
0

Hi Diego,

Sorry to ask again but i am receiving a divide by zero error? any suggestions as to why?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Scott,

Try this:

SELECT 
	T0.[CardCode] AS 'Customer ID', 
	T0.[CardName] AS 'Customer', 
	SUM(T0.[GrosProfit]) AS 'Gross Profit',
	SUM(T0.[DocTotal]-T0.[VatSum]) AS 'Total Value £', 
	(SUM(CASE WHEN (T0.DocTotal) <> 0 THEN T0.[GrosProfit] ELSE 0 END) / 
	 SUM(CASE WHEN T0.DocTotal <> 0 THEN T0.[DocTotal]-T0.[VatSum] ELSE 1 END)) * 100 AS  'GP %', 
	COUNT(T0.[DocNum]) AS 'Number of Orders' 
FROM 
	ORDR T0 
	INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] 
WHERE 
	T0.[DocDate] = convert(date, getdate()) 
	AND T0.[CANCELED] = 'N' 
GROUP BY 
	T0.[CardCode], T0.[CardName]

Kind Regards,

Diego Lother

1
Scott Cox Jan 13, 2017 at 02:42 PM
0

Thank you this edit seems to work correctly.

Appriciate your help

Share
10 |10000 characters needed characters left characters exceeded