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
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
Add a comment