Query..For...Invoice..and..A/R creditmemo..

[Former Member]

I wann a Query for Total Invoice amount for a particular custmer as well as A/R credit memo Total amount (EX if an custmer having 10 invoice and 5 a/r credit memo's i want sum of the 10 invoce amount as well as a/r credit memo total ).

Thanks & Regards

Yogi

[Former Member · Accepted solution]

Try this one - it gives you not only specific one but multiple Customers might be selected:

SELECT 'Invoice' AS 'TYPE', T0.CardCode, T0.CardName, SUM(T0.DocTotal) AS 'Total'

FROM dbo.OINV T0

WHERE T0.CardCode like '[%1\]%'

GROUP BY T0.CardCode, T0.CardName

UNION ALL

SELECT 'Credit Memo' AS 'TYPE', T0.CardCode, T0.CardName, -SUM(T0.DocTotal) AS 'Total'

FROM dbo.ORIN T0

WHERE T0.CardCode like '[%1\]%'

GROUP BY T0.CardCode, T0.CardName

ORDER BY T0.CardName

Thanks,

Gordon