I have this statement (below) but to my best effort I can not get the result I need. It is a report to tell me which product I need to restock. What is my current quantity in stock for that location, and that category of products. And is my quantity less then my reorder level for that product. Filter by category and location seem to work, but quantity less then reorder level is not.
What is it I need to change here to get this to work?
Thanks for all your help
ON 0_item_codes.stock_id = 0_stock_moves.stock_id
0_item_codes.inactive = 0
AND 0_item_codes.category_id = 16
AND 0_stock_moves.loc_code = 10
HAVING SUM(0_stock_moves.qty) >
( SELECT reorder_level
0_loc_stock.loc_code = 10
AND 0_loc_stock.stock_id = 0_item_codes.stock_id
Thanks for your response Rudy, The statement returns all the products in that category for that location. It is not filtering by the SUM of that product qty for less then the reorder level. In other words (bellow) item_code C152 should not be in the result as the SUM for id_code C152 in 0_stock_moves.qty for 0_stock_moves.loc_code “10” is 1 and the value in 0_stock_moves.reorder_level is 0
No worries, that’s why sometimes it is good to ask. You’re probably looking at the overall picture and trying to get everything working. A fresh set of eyes has to look at your problem without seeing it before so can find something like that.