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

[Former Member]

Hi

If you need in One line ,

SELECT CardCode, CardName,sum(Total) AS 'Total'

from

(Select 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 T1.CardCode, T1.CardName,Sum((T1.DocTotal *-1)) AS 'Total'

FROM dbo.ORIN T1

Where T1.CardCode like '[%1]%'

GROUP BY T1.CardCode, T1.CardName

) A

GROUP BY CardCode, CardName

ORDER BY CardName

<<

Or You might like this

SELECT CardCode, CardName,sum(Total) AS 'Total',Sum(ARtotal) as 'Invoice',Sum(APTotal) as 'Credit'

from

(Select T0.CardCode, T0.CardName, Sum (T0.DocTotal )as 'Total', Sum (Doctotal) as 'ARTotal',Null as 'APTotal'

FROM dbo.OINV T0

GROUP BY T0.CardCode, T0.CardName

UNION ALL

SELECT T1.CardCode, T1.CardName,Sum((T1.DocTotal *-1)) AS 'Total', Null as 'ARTotal', Sum((T1.DocTotal *-1)) AS 'APTotal'

FROM dbo.ORIN T1

GROUP BY T1.CardCode, T1.CardName

) A

GROUP BY CardCode, CardName

ORDER BY CardName

Hope this helps

Bishal

Edited by: BIshal Adhikari on Jul 22, 2009 9:07 AM

Edited by: BIshal Adhikari on Jul 22, 2009 9:24 AM

[Former Member]

select

(select sum(doctotal) from oinv where cardcode='CPCI')

+

(select sum(doctotal) from orin where cardcode='CPCI')

This should work

Thanks,

Radhakrishnan G

[Former Member]

Thanks For Reply

What U have give is working But I want group of custmer's at time (EX If i select a custmer code from 01 to 100 ).

Thanks & Regards

Yogi