I'm having trouble in extracting the last year figures based date entered. I'm actually would like to create a query where I'm able to know the "TOP 10 item sold based item category". I've created a query, which show the top10 item sold (total quantity & value) for this year, but not able to display the last year figure (quantity). Please advise & thanks for your help and time.
SET ROWCOUNT 10
SELECT T1.ItemCode, T2.ItemName, T3.ItmsGrpNam, SUM(T1.Quantity) as "Total Qty Sold", SUM(T1.TotalSumSy) as "Total Amount"
FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
WHERE T0.DocDate >='[%0]' AND T0.DocDate <='[%1]' AND T3.ItmsGrpNam ='[%A]'
GROUP BY T1.ItemCode, T2.ItemName, T3.ItmsGrpNam
ORDER by SUM(T1.Quantity) DESC
I wish to have the output as follow
Item Qty (2008) Qty (2007) Value(2008) Value(2007)
A 300 150 1000 500
B 250 300 800 650
C 100 250 700 550
Currently, My results display:
Item Qty (2008) Value(2008)
A 300 1000
B 250 800
C 100 700