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

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

78 Views

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.

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

Cheers,

Serene