Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Sales By Qty Query Change

Hello All --

Also for this Query would like to have option of selecting month at start of Query. Then Query only shows the month selected when run.

Thanks!!

Mike

SELECT T0.ITEMCODE,

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'JAN QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'FEB QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'MAR QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'APR QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'MAY QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'JUN QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'JUL QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'AUG QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'SEP QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'OCT QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'NOV QTY',

(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE =

T0.ITEMCODE) AS 'DEC QTY'

FROM [dbo].[OITM] T0

LEFT JOIN [dbo].[INV1] T1 ON T1.ItemCode = T0.ItemCode

WHERE T0.SellItem = 'Y'

GROUP BY T0.ItemCode,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

ORDER BY T0. ITEMCODE

Not what you were looking for? View more on this topic or Ask a question