cancel
Showing results for 
Search instead for 
Did you mean: 

Productwise Monthly Sales by a Customer

Former Member
0 Kudos

Dear Experts,

Can anybody help me how to get the monthly sales qty like April, May ....etc according to Customer wise for a perticular Item.

Any help to modify the existing query

SELECT T1.[ItemName] 'Item Name', T0.[CardName] 'Customer Name', T2.[City], T3.[GroupName] 'Category', SUM(T0.[OutQty]) as 'Sale Qty', SUM(T0.[InQty]) as 'Return Qty', SUM(T0.[OutQty])-SUM(T0.[InQty]) as 'Total Sale.Qty' FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode WHERE T0.[DocDate] >= [%0] and T0.[DocDate] <= [%1] and T0.[ItemCode] = [%2] and T0.[TransType] in ('15', '16', '14') GROUP BY T0.[CardName], T2.[City], T1.[ItemName], T3.[GroupName] order by T1.[ItemName], T3.[GroupName] desc

Regrds,

Srini

Accepted Solutions (1)

Accepted Solutions (1)

former_member186095
Active Contributor
0 Kudos

Hi Srini,

Have you tried to use XL reporter - Sales_per_Customer_by_Item ? You can enhance the template so that it meets your requirement.

Rgds,

JimM

Former Member
0 Kudos

Hi,

Any custom Query? bcz i need only net Qty sold

Regards

Srini

Answers (1)

Answers (1)

former_member204969
Active Contributor
0 Kudos

You may try something like this:

SELECT T1.ItemName 'Item Name', T0.CardName 'Customer Name',
 T2.City, T3.GroupName 'Category',
 datepart(m,T0.DocDate) Month,
 SUM(T0.OutQty) as 'Sale Qty', SUM(T0.InQty) as 'Return Qty',
 SUM(T0.OutQty)-SUM(T0.InQty) as 'Total Sale.Qty' 
FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
 INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
 INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode 
WHERE T0.DocDate >= [%0] and T0.DocDate <= [%1]
 and T0.ItemCode = '[%2]'
 and T0.TransType in ('15', '16', '14')
GROUP BY T0.CardName, T2.City, T1.ItemName, T3.GroupName,datepart(m,T0.DocDate)
order by T1.ItemName, T3.GroupName desc

Former Member
0 Kudos

Dear, Istvan,

Thanks for you r query But I am getting an error Arthimatic overflow while converting while executing.

Regards,

Srinivas

former_member186095
Active Contributor
0 Kudos

Hi,

you may test the Istvan query in your SQL server query --> demo datbase :




SELECT T1.ItemName 'Item Name', T0.CardName 'Customer Name',
 T2.City, T3.GroupName 'Category', T0.DocDate, 
 datepart(m,T0.DocDate) Month,
 SUM(T0.OutQty) as 'Sale Qty', SUM(T0.InQty) as 'Return Qty',
 SUM(T0.OutQty)-SUM(T0.InQty) as 'Total Sale.Qty' 
FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
 INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
 INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode 
WHERE 
T0.DocDate >= '01/01/2009' and T0.DocDate <= '12/30/2009'
and 
 T0.ItemCode = 'A00001'
 and T0.TransType in ('15', '16', '14')
GROUP BY T0.CardName, T2.City, T1.ItemName, T3.GroupName,datepart(m,T0.DocDate), T0.DocDate
order by T1.ItemName, T3.GroupName desc

JImM

former_member204969
Active Contributor
0 Kudos

If you are using version 8.8, try this:

SELECT T1.ItemName 'Item Name', T2.CardName 'Customer Name',
 T2.City, T3.GroupName 'Category',
 datepart(m,T0.DocDate) Month,
 SUM(T.OutQty) as 'Sale Qty', SUM(T.InQty) as 'Return Qty',
 SUM(T.OutQty)-SUM(T.InQty) as 'Total Sale.Qty' 
FROM OIVL T
 INNER JOIN OILM T0 On T.MessageID = T0.MessageID
 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
 INNER JOIN OCRD T2 ON T0.BPCardCode = T2.CardCode
 INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode 
WHERE T0.DocDate >= [%0] and T0.DocDate <= [%1]
 and T0.ItemCode = '[%2]'
 and T.TransType in ('15', '16', '14')
GROUP BY T2.CardName, T2.City, T1.ItemName, T3.GroupName,datepart(m,T0.DocDate)
Order by T1.ItemName, T3.GroupName desc

(The OINM only a view in this version, these tables contain the relevant information.)