Query ammenmend

[former_member209725]

Dear Experts,

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.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi.,

select distinct a.cardcode,a.cardname,a.doctotal,b.doctotal 'Credit Memo'  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

left JOIN

(select distinct T0.cardcode,T0.cardname,sum(T0.doctotal)as doctotal from  ORPC T0

where t0.docdate >= '20120401' and T0.docdate <= '20130331'

group by T0.cardcode,T0.cardname)b on a.cardcode=b.cardcode

you will use the above query

i hope this will help you

Thanks

Madeswaran P

[former_member209725 · Accepted solution]

Hi Madeswaran,

Thanx for your valueable reply. Query is running good.

Regards,

Ravi.

[VamcParimisetti]

Hi Ravi,

Try This :

SELECT DISTINCT T0.[DocNum] ' AP INV NO', T0.[DocDate] 'AP INV DATE',

T0.[CardCode] 'AP VENDOR CODE', T0.[CardName] 'AP VENDOR NAME', T0.[VatSum] 'AP TAX SUM', T0.[DocTotal] 'AP INVOICE TOTAL',

T2.[DocNum] 'DEBIT NOTE NO', T2.[DocDate] 'DEBIT NOTE DATE', T2.[CardCode] 'DEBIT NOTE VEN CODE',

T2.[CardName] 'DEBIT VEN NAME', T2.[VatSum] 'DEBITNOTE TAX SUM', T2.[DocTotal] 'DEBIT NOTE TOTAL'

FROM OPCH T0 

INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN RPC1 T3 ON T3.BASEENTRY=T0.DOCENTRY

LEFT JOIN ORPC T2 ON T2.DOCENTRY=T3.DOCENTRY

WHERE  (T0.[DocDate] between  '[%0]' and '[%1]') OR ( t2.docdate between '[%0]' and '[%1]')

Hope Helpful,

Vamsi