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