Skip to Content
0
Oct 21, 2011 at 08:35 AM

sales per item report

14 Views

hello experts

i have made the following query but i dont want to display double and triple times values per itemcode

i actually want to display the sum the quantity and the sum of the linetotal per item

SELECT 
--T0.CardName
-- T0.ShipToCode
-- T0.DocDate
 T1.ItemCode
, T1.Dscription
, sum(T1.Quantity)
, T1.LineTotal AS 'u0391u03BEu03AFu03B1 u03C0u03CEu03BBu03B7u03C3u03B7u03C2'
--, T2.CardCode 
FROM OINV T0  
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
INNER JOIN OSLP T3 ON T0.SLPCODE=T3.SLPCODE 
WHERE 
--(T0.CardName >= [%0] or [%0] = ' ') 
--and (T0.CardName<= [%1] or [%1] = ' ') 
--and (T0.ShipToCode >= [%2] or [%2] = ' ') 
--and (T0.ShipToCode <= [%3] or [%3] = ' ') 
(T2.CardCode = '80503')
--and (T2.CardCode = [%4] or [%4] = ' ') 
AND (T0.DOCDATE BETWEEN '2011-10-01' AND '2011-10-25') 
--AND (T0.DOCDATE BETWEEN '[%5]' AND '[%6]') 
--and (T0.CANCELED= 'N') 
--and ((T3.SLPNAME BETWEEN '[%7]'AND '[%8]') OR ('[%7]'=' ' AND  '[%8]'=' '))
group by T0.CardName,  T0.DocDate, T1.ItemCode, T1.Dscription, T1.Quantity, T1.LineTotal, T2.CardCode 
order by T1.ItemCode 

60344 test item1 1.000000 0.880000

60360 test item2 1.000000 1.500000

60360 test item2 1.000000 1.500000

60360 test item2 2.000000 3.010000

60361 test item3 2.000000 3.010000

do you have any idea?

thanks in advance for your time!