Skip to Content
Former Member
Jul 24, 2008 at 04:06 AM

- How to Display Sales Qty & Value for This Year & Last Year in 2 Columns -


Dear All,

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.


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