Skip to Content
0
Jul 20, 2023 at 10:10 AM

Stock Reorder report query based on sales analysis/figuires

67 Views

Hi All,

Am trying to develop a report that shows how much to order and when based on sales figures each day/Monthly.

Basically I do not want to run out of stock or my stock to reach a certain minimum quantity. What I want to see is if the quantity hit a certain minimum, I must then reorder to cover sales.

I have a query

SELECT Distinct P.[WhsCode], P.[Item No.], P.[OnHand], P.[IsCommited] as 'Allocated', P.[OnOrder] as 'On Order', [7] as [Jul], [8] as [Aug], [9] as [Sep], [10] as [Oct], [11] as [Nov], [12] as [Dec], [1] as [Jan], [2] as [Feb], [3] as [Mar], [4] as [Apr], [5] as [May], [6] as [Jun]

FROM (SELECT(T0.ItemCode) as 'Item No.', T1.[WhsCode], T0.[OnHand], T0.[IsCommited], T0.[OnOrder],T0.[MinStock], T0.[MaxStock],T1.[Quantity], MONTH(T1.DocDate) as [Month]

FROM dbo.OITW T0

INNER JOIN INV1 T1 ON T1.[ItemCode] = T0.[ItemCode] and T1.[WhsCode] = T0.[WhsCode] AND YEAR(T1.[DocDate]) = YEAR (GETDATE ())

WHERE T1.[WhsCode] = [%2] and T0.[ItemCode] BETWEEN [%0] AND [%1]) S

PIVOT (SUM(Quantity) FOR [Month] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P

ORDER BY P.[Item No.]

The query doesn't show me the min and max stock. Also I want it to give me an alert when stock reach its minimum point to reorder.

Regards

Mo