Skip to Content

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

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)
Add comment
10|10000 characters needed 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

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 19, 2016 at 03:17 AM

    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

    Add comment
    10|10000 characters needed characters exceeded