Skip to Content

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

Sales By Amt Query Change

Hello All -

We have this Query used to view sales by Amt for each month. Can we modify it so it does not show every month. Instead, gives option to select the month at the start of the Query and only shows the month selected in the results?

Keep everything else the same.

Thanks!!

Mike

SELECT T0.ITEMCODE,

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'JAN Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'FEB Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'MAR Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'APR Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'MAY Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'JUN Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'JUL Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'AUG Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'SEP Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'OCT Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'NOV Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

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

= T0.ITEMCODE) AS 'DEC Amt'

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

replied

Hi Suda,

No need for Views, Stored Procedures or querying the Posting Periods table OFPR.

Mike - have a look at my response to your other post titled 'Sales By Qty Query Change'. It's fairly basic but should give you what you need.

Regards,

Andrew.

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