Skip to Content
0

How can i get the onhand quantity of the item with date?

Nov 18, 2016 at 07:05 AM

82

avatar image

Hi Experts,

I already posted the same question before but got deleted, i was told that someone's already asked the same question but upon browsing i don't see any.

So my question is how can i get the onhand quantity per item,warehouse with date range?
I already know about the OINM SUM(INqty - Outqty) like on scenario 2 below but thats not the result that i wanted. I want to have an output like on the scenario 1

capture.png (13.9 kB)
10 |10000 characters needed characters left characters exceeded

Hi Mike,

I removed the TAG Microsoft SQL Server, because it is used for Questions and Blogs about SAP systems based on SAP Netweaver on SQL Server.

Your product., based on the user tag "sap b1" I believe (and by the structure), that is BusinessOne. I updated that for this.

Best regards,

Luis

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
DIEGO LOTHER Nov 19, 2016 at 03:17 AM
0

Hi Mike,

A sample. I believe that is what you want.

SELECT 
	ItemCode,
	Warehouse,
	DocDate,
	SUM(OnHand) OVER (ORDER BY DocDate) AS OnHand
FROM 
	(
	SELECT 
		ItemCode,
		Warehouse,
		DocDate,
		SUM(InQty - OutQty) AS OnHand
	FROM 
		OINM T0
	WHERE
		T0.ItemCode = '[%0]'
		AND T0.DocDate BETWEEN [%1] and [%2]
	GROUP BY
		ItemCode, Warehouse, DocDate
		) T0
ORDER BY T0.DocDate

Hope it helps.

Kind Regards,

Diego Lother

Share
10 |10000 characters needed characters left characters exceeded