I am having a query in which i can see AP Invoice sum of Doctotal for all vendors and was needed debit note sum(doctotal) with AP Invoice Total if any.
Query is :
select distinct a.cardcode,a.cardname,a.doctotal from
(select distinct T0.cardcode,T0.cardname,sum(T0.doctotal)as doctotal from opch T0
where t0.docdate >= '20120401' and T0.docdate <= '20130331'
group by T0.cardcode,T0.cardname) a
Any help will be highly appreciated.