Sales By Amt Query Change

[Former Member]

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

[Former Member · Accepted solution]

Mike,

You can try this:

SELECT T0.ITEMCODE, SUM(T1.QUANTITY*T1.PRICE) 
AS 'Selected Month Amt'
FROM dbo.OITM T0
LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode 
AND T1.DOCDATE Between '[%0]' and '[%1]'
WHERE T0.SellItem = 'Y' 
GROUP BY T0.ItemCode
ORDER BY T0.ItemCode

Thanks,

Gordon

[Former Member · Accepted solution]

Thanks Gordon --

But I was actually looking for a list of months for the selection rather than dates. Then, when Query has pulled the data -- at the top header it would show the month selected rather than say Selected Period.

Can this be done?

Thanks!

Mike

[former_member583013 · Accepted solution]

This would not be possible through a simple SQL. You will need a combination of a VIEW or Stored Procedure.

The Base query for getting the Start and Ending Month

DECLARE @FROM VARCHAR(10), @TO VARCHAR(10)
/* SELECT FROM  [dbo].[OFPR] T0 WHERE */ SET @FROM = /* T0.Code */ '[%0]' 
/* SELECT FROM  [dbo].[OFPR] T0 WHERE */ SET @TO = /* T0.Code */ '[%1]' 
SELECT (SELECT T0.F_RefDate FROM  [dbo].[OFPR] T0 WHERE T0.Code = @FROM) [FROM MONTH], (SELECT T0.T_RefDate FROM  [dbo].[OFPR] T0 WHERE T0.Code = @TO) [TO MONTH]

[Former Member · Accepted solution]

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.