Skip to Content
0

SAP Business One - query inventory qty sold per month

Nov 01, 2016 at 07:55 PM

54

avatar image

Hello SAP Gurus,

Can someone please help with this query to show inventory quantities sold per month for past 12 months?

Right now it just shows everything that has sold in it's entire history.

Here is the query:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], SUM(T1.[Quantity]) FROM [dbo].[OITM] T0 INNER JOIN INV1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry GROUP BY T0.[ItemCode], T0.[ItemName], T0.[OnHand]

Thank you very much in advance for any help.

It is much appreciated!!

I found it on the forums and here is what I was looking for:

  1. SELECT P.[ItemCode], P.[ItemName], P.[OnHand],
  2. [1] as [Jan],
  3. [2] as [Feb],
  4. [3] as [Mar],
  5. [4] as [Apr],
  6. [5] as [May],
  7. [6] as [Jun],
  8. [7] as [Jul],
  9. [8] as [Aug],
  10. [9] as [Sep],
  11. [10] as [Oct],
  12. [11] as [Nov],
  13. [12] as [Dec]
  14. FROM (
  15. SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], T1.[Quantity],MONTH(T2.docdate) as [MONTH]
  16. FROM [dbo].[OITM] T0
  17. INNER JOIN dbo.INV1 T1 ON T0.ItemCode = T1.ItemCode
  18. INNER JOIN dbo.OINV T2 ON T1.DocEntry = T2.DocEntry AND Year(T2.docdate)=2010 ) S
  19. PIVOT (SUM(Quantity) FOR [month] IN
  20. ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
  21. ORDER BY P.[ItemCode]

Thanks Gordon!!

Kind regards,

Larry T.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Nagarajan K Nov 01, 2016 at 11:10 PM
0

Hi,

I am getting result from above query. Please explain your issue with query.

Thanks

Share
10 |10000 characters needed characters left characters exceeded
Edmund Leung Nov 02, 2016 at 02:22 AM
0

SELECT P.[ItemCode], P.[ItemName], P.[OnHand], [1] as [Jan], [2] as [Feb], [3] as [Mar], [4] as [Apr], [5] as [May], [6] as [Jun], [7] as [Jul], [8] as [Aug], [9] as [Sep], [10] as [Oct], [11] as [Nov], [12] as [Dec] FROM ( SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], T1.[Quantity],MONTH(T2.docdate) as [MONTH] FROM [dbo].[OITM] T0 INNER JOIN dbo.INV1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN dbo.OINV T2 ON T1.DocEntry = T2.DocEntry --AND Year(T2.docdate)=2010 ) S PIVOT (SUM(Quantity) FOR [month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P ORDER BY P.[ItemCode]

Try to remove the year criteria first, and check any result.

Share
10 |10000 characters needed characters left characters exceeded