Skip to Content
0
Former Member
Nov 09, 2011 at 06:06 AM

Sales query

19 Views

hai

Blow this query i need a customer name how do i add pls reply soon , above this query mothly sales item wise report

Pls reply soon

select a1.[PART NO],a1.[PART DESCRIPTION],sum(a1.Quantity)[Qunatity],sum(a1.[BASE AMOUNT])[Total],

sum(a1.[BED 10%])[Excise],sum(a1.[ECESS 2%])[Cess],SUM(a1.[HSCESS 1%])[Hcess],SUM(a1.CST)[Cst],SUM(a1.VAT)[Vat],

SUM(a1.TOTAL)[Total]

from(

SELECT DISTINCT(T0.[DocNum]) AS 'INVOICE NUMBER', T0.[DocDate] AS 'INVOICE DATE', T0.[CardName] AS 'NAME OF THE PARTY',

T3.[City][City],T1.[ItemCode] AS 'PART NO', T1.[Dscription] AS 'PART DESCRIPTION',

T1.[Quantity], T1.[Price], T1.[LineTotal] AS 'BASE AMOUNT',

(SELECT sum(TaxSum) FROM OEI4 where statype=-90 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'BED 10%',

(SELECT sum(TaxSum) FROM OEI4 where statype=-60 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'ECESS 2%',

(SELECT sum(TaxSum) FROM OEI4 where statype=-55 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'HSCESS 1%',

(SELECT sum(TaxSum) FROM OEI4 where statype=4 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'CST',

(SELECT sum(TaxSum) FROM OEI4 where statype=1 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'VAT',

T1.[LineTotal]+t1.[VatSum] as'TOTAL' FROM OOEI T0 INNER JOIN OEI1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OEI4 T2 ON T0.DocEntry = T2.DocEntry

left JOIN OCRD T3 ON T3.CardCode=T0.CardCode

WHERE T1.[TargetType] ='-1' and T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND (T0.[CardName] ='[%2]' OR '[%2]'=' ') AND (T1.[Dscription] ='[%3]' OR '[%3]'=' ')AND T0.[Series]<>'52' AND T1.[LocCode]<>'1'

) A1 group by a1.[PART NO],a1.[PART DESCRIPTION]

union all

select a1.[PART NO],a1.[PART DESCRIPTION],sum(a1.Quantity)[Qunatity],sum(a1.[BASE AMOUNT])[Total],

sum(a1.[BED 10%])[Excise],sum(a1.[ECESS 2%])[Cess],SUM(a1.[HSCESS 1%])[Hcess],SUM(a1.CST)[Cst],SUM(a1.VAT)[Vat],

SUM(a1.TOTAL)[Total]

from(

SELECT DISTINCT(T0.[DocNum]) AS 'INVOICE NUMBER', T0.[DocDate] AS 'INVOICE DATE', T0.[CardName] AS 'NAME OF THE PARTY',

T3.[City][City],T1.[ItemCode] AS 'PART NO', T1.[Dscription] AS 'PART DESCRIPTION',

T1.[Quantity], T1.[Price], T1.[LineTotal] AS 'BASE AMOUNT',

(SELECT sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'BED 10%',

(SELECT sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'ECESS 2%',

(SELECT sum(TaxSum) FROM INV4 where statype=-55 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'HSCESS 1%',

(SELECT sum(TaxSum) FROM INV4 where statype=4 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'CST',

(SELECT sum(TaxSum) FROM INV4 where statype=1 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'VAT',

T1.[LineTotal]+t1.[VatSum] as'TOTAL' FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN INV4 T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN OCRD T3 ON T3.CardCode=T0.CardCode

WHERE T1.[TargetType] ='-1' and T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND (T0.[CardName] ='[%2]' OR '[%2]'=' ') AND (T1.[Dscription] ='[%3]' OR '[%3]'=' ') AND T0.[Series]='41'

) A1 group by a1.[PART NO],a1.[PART DESCRIPTION]