Skip to Content
0
Former Member
Feb 19, 2014 at 03:25 PM

SQL Query to report on Minimum Stock Levels

259 Views

SQL Experts,

I want to run a report to show me a list of items that are below minimum stock but takes in to account what is On purchase order. I have the following query:

SELECT T0.[ItemCode], T1.[ItemName], T0.[WhsCode], T0.[OnHand], T0.[OnOrder], T0.[IsCommited] as Committed, T0.[MinStock],T0.[IsCommited] - T0.[OnHand] - T0.[OnOrder] + T0.[MinStock] Shortfall FROM OITW T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[MinStock] is not null and T0.[MinStock] <> 0 and (T0.[OnHand] + T0.[OnOrder]) - (T0.[IsCommited] ) < T0.[MinStock] and T1.[frozenFor] <> 'y'

It runs but only returns 1 value which I don't believe to be correct. Any ideas?

Thanks