Skip to Content

SAP Business One - query inventory qty sold per month

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 01, 2016 at 11:10 PM

    Hi,

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

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 02, 2016 at 02:22 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded