Skip to Content
0
Former Member
Nov 15, 2010 at 08:41 AM

vat %

13 Views

hi,

i this query i want to add vat 4 % seperate column and vat 12.5% in seperate column.. likewise cst 2% and 12.5% seperate column.. help me to solve this query

SELECT M.DocNum AS 'AR Inv. #', M.DocDate as 'Date', M.CardName as 'Customer Name', numatcard as'Customer Ref No',M.Doccur as 'Currency',

(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry) as 'Base Amt.(Rs.)',

(SELECT Sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=M.DocEntry) as 'BED (Rs.)',

(SELECT Sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=M.DocEntry) as 'Cess (Rs.)',

(SELECT Sum(TaxSum) FROM INV4 where statype=-55 and DocEntry=M.DocEntry) as 'HECess (Rs.)',

(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry) +

(SELECT Sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=M.DocEntry) +

(SELECT Sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=M.DocEntry) +

(SELECT Sum(TaxSum) FROM INV4 where statype=-55 and DocEntry=M.DocEntry) as 'Total Duty',

(SELECT Sum(TaxSum) FROM INV4 where statype=1 and DocEntry=M.DocEntry) as ' VAT (Rs.) ',

(SELECT Distinct TaxRate FROM INV4 where statype=1 and DocEntry=M.DocEntry) as ' VAT RATE % ',

(SELECT Sum(TaxSum) FROM INV4 where statype=4 and DocEntry=M.DocEntry) as ' CST (Rs.) ',

(SELECT Distinct TaxRate FROM INV4 where statype=4 and DocEntry=M.DocEntry) as ' CST RATE % ',

(Select Sum(LineTotal) From INV3 Q Where Q.DocEntry=M.DocEntry) AS 'Freight (Rs.)',

M.DocTotal as 'Total (Rs.)'

FROM OINV M LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry

LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum

LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry

LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry

WHERE (M.DocDate >= '[%0]' AND M.DocDate <= '[%1]') and M.doctype='[%2]'

GROUP BY

M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.Doccur,M.DocTotal

ORDER BY

M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.Doccur,M.DocTotal

regards,

Vignesh.R