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]
Hi,
Check this
select a1.[Business Partner Code] as 'Cardcode', a1.[PART NO] as 'NO',a1.[PART DEscription] as 'DESCRIPTION',sum(a1.[Quantity]) as 'Qunatity',sum(a1.[BASE AMOUNT]) as 'Total', sum(a1.[BED 10%]) as 'Excise',sum(a1.[ECESS 2%])as 'Cess',SUM(a1.[HSCESS 1%]) as 'Hcess',SUM(a1.[CST]) as 'Cst',SUM(a1.[VAT]) as 'Vat', SUM(a1.TOTAL)as 'Total' from( SELECT DISTINCT(T0.DocNum) AS 'INVOICE NUMBER', T0.DocDate AS 'INVOICE DATE', T0.CardName AS 'NAME OF THE PARTY', T3.CityCity,T1.ItemCode AS 'PART NO', T1.Dscription AS 'PART DESCRIPTION', T1.Quantity, T1.Price, T1.LineTotal AS 'BASE AMOUNT', T3.cardcode as 'Business Partner Code', (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 DESCRIPTION],a1.[PART NO],a1.[Business Partner Code] Union all select a1.[Business Partner Code] as 'Cardcode', a1.[PART NO] as 'NO',a1.[PART DEscription] as 'DESCRIPTION',sum(a1.[Quantity]) as 'Qunatity',sum(a1.[BASE AMOUNT]) as 'Total', sum(a1.[BED 10%]) as 'Excise',sum(a1.[ECESS 2%])as 'Cess',SUM(a1.[HSCESS 1%]) as 'Hcess',SUM(a1.[CST]) as 'Cst',SUM(a1.[VAT]) as 'Vat', SUM(a1.TOTAL)as 'Total' from( SELECT DISTINCT(T0.DocNum) AS 'INVOICE NUMBER', T0.DocDate AS 'INVOICE DATE', T0.CardName AS 'NAME OF THE PARTY', T3.City,T1.ItemCode AS 'PART NO', T1.Dscription AS 'PART DESCRIPTION', T1.Quantity, T1.Price, T1.LineTotal AS 'BASE AMOUNT', T3.cardcode as 'Business Partner Code', (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'=' ') ) a1 group by a1.[PART DESCRIPTION],a1.[PART NO],a1.[Business Partner Code]
Kind Regards,
Jitin
SAP Business One Forum Team
Add a comment