SAP Business One - query inventory qty sold per month

[larryenet]

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.

[Former Member]

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.

[kothandaraman_nagarajan]

Hi,

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

Thanks