Skip to Content

GP Percentage Help

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]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Jan 04, 2017 at 06:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06, 2017 at 05:06 PM

    Hi Diego,

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

    Regards

    Scott

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 10, 2017 at 09:41 AM

    Hi Diego,

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

    Add comment
    10|10000 characters needed 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

  • Jan 13, 2017 at 02:42 PM

    Thank you this edit seems to work correctly.

    Appriciate your help

    Add comment
    10|10000 characters needed characters exceeded