SQL Query to report on Minimum Stock Levels

[Former Member]

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

[kothandaraman_nagarajan · Accepted solution]

Hi Tim,

Try this query:

SELECT     dbo.OITW.WhsCode, dbo.OITW.ItemCode, dbo.OITM.ItemName AS ItemDescription, dbo.OITW.OnHand, dbo.OITW.IsCommited, dbo.OITW.OnOrder,

                      dbo.OITW.MinStock, dbo.OITW.MinOrder, dbo.OITW.MaxStock, dbo.OITW.OnHand - dbo.OITW.MinStock AS QtyAboveMin

FROM         dbo.OITW INNER JOIN

                      dbo.OITM ON dbo.OITW.ItemCode = dbo.OITM.ItemCode

WHERE     (dbo.OITW.OnHand - dbo.OITW.MinStock <= 0) AND (dbo.OITW.MinStock > 0)

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

That's really good man, thanks.

[Former Member]

Hi,

Try:

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  and  T1.[frozenFor] <> 'y'

WHERE isnull(T0.[MinStock],0) <> 0 and (T0.[OnHand] + T0.[OnOrder] - T0.[IsCommited] ) <  T0.[MinStock]

How many lines do you get? Have you defined warehouse level minstock?

Thanks,

Gordon

[Former Member]

Thanks Gordon. It returns the same 1 line. The problem may be with my data so I need to investigate. If I run this 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 sort by Shortfall there are 25 lines with a shortfall of > 0

Yes using minimum stock by warehouse.

[Former Member]

This query includes 0 minstock items.