on 11-15-2010 8:41 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.