Skip to Content
0
Jan 04, 2017 at 04:51 PM

GP Percentage Help

97 Views

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]