cancel
Showing results for 
Search instead for 
Did you mean: 

vat %

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

kvbalakumar
Active Contributor
0 Kudos

Hi Vignesh,

Try this

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 TaxRate= 4 and DocEntry=M.DocEntry) as ' VAT 4% (Rs.) ',
(SELECT Sum(TaxSum) FROM INV4 where statype=1  and TaxRate= 1 and DocEntry=M.DocEntry) as ' VAT 1% (Rs.) ',
(SELECT Sum(TaxSum) FROM INV4 where statype=4 and TaxRate= 2 and DocEntry=M.DocEntry) as ' CST 2% (Rs.) ',
(SELECT Sum(TaxSum) FROM INV4 where statype=4 and TaxRate= 12.5 and DocEntry=M.DocEntry) as ' CST 12.5% (Rs.) ',
(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,

Bala

Former Member
0 Kudos

solved.thanks a lot bala...

Answers (0)