I have written a basic query, as below, to give me total sales figures. From using the fields below, I can calculate total sales, taking all credits into account.
SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[VatSum], T0.[TotalExpns], T1.[SlpName] FROM OINV T0 INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], -T0.[DocTotal], -T0.[VatSum], -T0.[TotalExpns], T1.[SlpName] FROM ORIN T0 INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
However, I've hit a problem. When an AR invoice has been raised and then cancelled, the AR cancellation document has been included in the results from the below query as a debit and not a credit, effectively doubling the sales figure for that particular customer. How do I get AR cancellation documents to show as 'minus?'
(And I know the Sales Analysis on SAP calculates this correctly but I am using sql to produce a report in another piece of reporting software we have so I can't use this).
Any help would be appreciated.