Stock in warehouse report

[former_member509679]

Hi All,

Wondering if you are able to help with a report, we have stock on our shelves and excess stock locations, i am looking to make a query that shows when we need to move the stock from excess to normal shelves.

item code, desc, total qty per bin location in excess, Excess Bin location, total qty in normal shelves, Normal Shelf location, qty to move.

The qty to move should be based on sales so for example if we have a weekly average is 100 items sold, then this should be displayed.

I have this so far but i am stuck getting the other locations in.

the locations in the query are our current excess locations.

SELECT 
	T0.ItemCode, 
	T5.ItemName, 
	T0.WhsCode,
	SUM(T2.OnHandQty) as 'Total Stock Bin',
	SUM(T0.OnHand) AS 'Total Stock Warehouse',
	T1.BinCode,
	(SELECT SUM(T6.Quantity)/4 FROM INV1 T6  WHERE T6.ItemCode=T5.ItemCode and T6.DocDate>=(GetDate()-30)) AS 'Average over 1 month'


FROM 
	OITW T0
	INNER JOIN OBIN T1 ON T0.WhsCode = T1.WhsCode
	LEFT OUTER JOIN OIBQ T2 ON T2.WhsCode = T0.WhsCode AND T1.AbsEntry = T2.BinAbs AND T0.ItemCode = T2.ItemCode
	INNER JOIN OITM T5 ON T5.ItemCode = T0.ItemCode
WHERE 
	T2.OnHandQty > 0 
	AND T1.BinCode between 'EW-91-A-1' and 'EW-93-T-5' 
	AND T2.OnHandQty > 0
GROUP BY 
	T0.ItemCode, T5.ItemName, T0.WhsCode, 
	T0.WhsCode, T1.BinCode,  t5.itemcode
ORDER BY T0.ItemCode, T0.WhsCode, T1.BinCode