Former Member

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

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

10|10000 characters needed characters exceeded

### Related questions

Former Member
Posted on Jul 22, 2009 at 03:21 PM

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

10|10000 characters needed characters exceeded
• Former Member
Posted on Jul 22, 2009 at 07:07 AM

select

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

+

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

This should work

Thanks,

10|10000 characters needed characters exceeded
• Former Member

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

• Former Member
Posted on Jul 22, 2009 at 04:07 PM

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