Former Member

# Query

Hi

SELECT M.CardCode,M.CardName as 'Vendor Name',

(SELECT Sum(TaxSum) FROM PCH4 where (statype=1 or statype=7) and relatetype = 1 and DocEntry=M.DocEntry) as ' AmountOfTax'FROM OPCH M Inner JOIN PCH1 L on L.DocEntry=M.DocEntry

Inner JOIN PCH4 T on T.DocEntry=L.DocEntry

Inner JOIN PCH12 T0 on T.DocEntry=L.DocEntry

INNER JOIN OLCT T2 ON L.LocCode = T2.Code

WHERE M.[DocDate] >= [%1] and M.[DocDate] <= [%2] and t.statype = 1 GROUP BY

M.CardCode,M.CardName,M.DocEntry, T.stccode ORDER BY

M.CardName,M.DocEntry

Problem i am facing is if there are 2 type of tax code in doc. no it shows sum of taxsum in both not taxcode wise

Thanks

10|10000 characters needed characters exceeded

### Related questions

Former Member
Posted on Jul 05, 2010 at 06:28 PM

Hi,

Try this:

SELECT M.CardCode,M.CardName as 'Vendor Name',

(SELECT Sum(TaxSum) FROM PCH4 where statype=1 and relatetype = 1 and DocEntry=M.DocEntry) as ' AmountOfTax1',

(SELECT Sum(TaxSum) FROM PCH4 where statype=7 and relatetype = 1 and DocEntry=M.DocEntry) as ' AmountOfTax2'

FROM OPCH M Inner JOIN PCH1 L on L.DocEntry=M.DocEntry

Inner JOIN PCH4 T on T.DocEntry=L.DocEntry

Inner JOIN PCH12 T0 on T.DocEntry=L.DocEntry

INNER JOIN OLCT T2 ON L.LocCode = T2.Code

WHERE M.DocDate >= [%1\] and M.DocDate <= [%2\] and (t.statype = 1 or t.statype = 7)

GROUP BY

M.CardCode,M.CardName,M.DocEntry, T.stccode

ORDER BY

M.CardName,M.DocEntry

Thanks,

Gordon

10|10000 characters needed characters exceeded
• Former Member
Posted on Jul 06, 2010 at 04:47 AM

Closed.