0

# GP Percentage Help

Jan 04, 2017 at 04:51 PM

43

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]

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

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

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

Thank you this edit seems to work correctly.