Sales Analysis Query Help

Hi Juan........

Please try this.......

SELECT SUBSTRING((CONVERT(VARCHAR(11),T0.DOCDATE,106)),4,11), T0.[DocNum], T0.[DocDate], Sum(T1.[Quantity]) 'Quantity', Sum(T1.[LineTotal]) 'Amount',(Sum(T1.[LineTotal])/Sum(T1.[Quantity])) 'Rate', T0.[TotalExpns] 'Pack/Forw.', t0.doctotal-t0.vatsum as 'Mat.Value', (isnull((SELECT SUM((case when upper(t4.STACode) like 'BED%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'Cenvat 10%', 
(isnull((SELECT SUM((case when upper(t4.STACode) like 'ECESS%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'Edu. Cess 2%',
	(isnull((SELECT SUM((case when upper(t4.STACode) like 'HSC%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'S&H Cess 1%', 
(isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' and t4.TaxRate=4 then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'VAT4%',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'AVAT%' and t4.TaxRate=1 then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'VAT1%'
 FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
Where T0.DocDate>='[%0]' and T0.DocDate<='[%1]'
Group By T0.[DocNum], T0.[DocDate], T0.[TotalExpns], t0.doctotal, t0.vatsum, T0.DocEntry
Order By T0.[DocDate]

Regards,

Rahul

Hi,

Try this.... Put Your VAT Tax code statype here and modify the query as per your requirement...

DECLARE @startDate DATETIME, @endDate DATETIME, @dummy INTEGER
SELECT TOP 1 @dummy = DocNum  from OINV T0 where T0.DocDate >=[%1] and T0.DocDate <= [%2]
Select @startDate='[%1]' ,@endDate = '[%2]'
 SELECT DISTINCT T0.Docentry, 
T0.[DocNum] as 'Bill No.', 
CONVERT(VARCHAR(10), T0.[DOCDATE], 3) AS DOCDATE, T0.[CardName] as 'Customer Name' , T1.[ItemCode] as 'Item Code',  T1.[Dscription] as 'Item Description',  T1.[Quantity],T1.[Price] as 'Price', T1.[TaxCode],
( T1.LineTotal ) as 'Line Total' ,
T0.[TotalExpns] as 'P&F',
 (T1.LineTotal + T0.[TotalExpns]) AS 'Basic',
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=1 and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)VAT,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=4 and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)CST,
 T0.DocTotal as 'Doc Total' , T0.[Comments] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
 WHERE T0.DocDate >=@startDate and T0.DocDate <= @endDate

Regards,

Sudhir B.