cancel
Showing results for 
Search instead for 
Did you mean: 

Sales By Qty Query Change

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

UDT means User Defined Table. You need to create and maintain it through Tools - User Tables.

Former Member
0 Kudos

Similar with the other. Try this:


SELECT T0.ITEMCODE, SUM(T1.QUANTITY) 
AS 'Selected Month Qty'
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
0 Kudos

Thanks Gordon --

Same comment as the other one, 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
Active Contributor
0 Kudos

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
0 Kudos

Hi Mike,

Try the following:

- Create a UDT called MonthList (or whatever you want).

- Populate the Code column with month numbers and the Name column with the month names e.g. Code = 1 and Name = Jan, Code = 2 and Name = Feb and so on.

- Then use the following query to get your item values per the month you select:


DECLARE @Mth AS VARCHAR(30), @MthNum AS SMALLINT
SELECT @MthNum = CAST(T0.Code AS SMALLINT) FROM [dbo].[@MonthList] T0 WHERE T0.Name = '[%0]' 
SELECT @Mth = T1.Name FROM [dbo].[@MonthList] T1 WHERE CAST(T1.Code AS SMALLINT) = @MthNum 

SELECT CASE WHEN (GROUPING(T0.ITEMCODE) = 1) THEN 'TOTAL QTY - ' + @Mth 
ELSE T0.ITEMCODE END [ItemCode], SUM(T1.QUANTITY) AS 'Mthly Qty' 
FROM dbo.OITM T0
LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode
WHERE T0.SellItem = 'Y' AND MONTH(T1.DocDate) = @MthNum 
GROUP BY T0.ItemCode WITH ROLLUP

I have added in an extra TOTAL row that displays the total quantity and the month name you selected.

The only issue with this is because the Name column in a UDT is alphanumeric, when you click the parameter selection button the months appear alphabetically e.g. Apr, Aug, Dec...

If you wanted you could get rid of the extra row and just display the selected month as an extra column. You can also populate the UDT with the full month names if you wish.

Just change the SUM(T1.Quantity) to SUM(T1.Quantity*T1.Price) and any descriptions from 'Qty' to 'Amt' etc for your other query.

Hope that helps.

Regards,

Andrew.

Former Member
0 Kudos

Hi Andrew --

Is a UDT the same as a UDF?

Thanks!

Mike

Former Member
0 Kudos

Hi Mike,

As mentiond above, a UDT is a table created inside SAP that can store whatever you need.

- In SBO2007 create a UDT by going to Tools -> Customisation Tools -> User-Defined Tables Setup. Enter a name and description and leave Object Type as No Object. Then go to Tools -> User-Defined Windows, select your table and populate.

- In SBO2005 create a UDT by going to Tools -> User-Defined Fields -> Manage User Fields. Select the User Tables button at the bottom and enter a name and description as above. Then go to Tools -> User Tables, select your table and populate.

Regards,

Andrew.

jitin_chawla
Advisor
Advisor
0 Kudos

Hi,

Check this :

SELECT T0.ITEMCODE, SUM(T1.QUANTITY)

AS 'Selected Month Quantity'

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

Regards,

Jitin