Quantity > reorder level not working

Databases
1

Hi guys

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 :slight_smile:

AM


SELECT
         0_item_codes.stock_id
,        0_item_codes.description
FROM
         0_item_codes
  JOIN  0_stock_moves
     ON 0_item_codes.stock_id = 0_stock_moves.stock_id
WHERE
         0_item_codes.inactive = 0
   AND 0_item_codes.category_id = 16
   AND 0_stock_moves.loc_code = 10

 GROUP BY
    0_item_codes.stock_id
HAVING SUM(0_stock_moves.qty) >
 (    SELECT reorder_level
         FROM
     0_loc_stock
        WHERE
             0_loc_stock.loc_code = 10
    AND 0_loc_stock.stock_id = 0_item_codes.stock_id
)
2

I did not bother to strip out any of the data so the file is a little big to paste here in the forum. So I put it here: http://almacennaval.com/file_share/test_tables.sql

3

could you dump the tables please? i.e. generate the three CREATE TABLE statements as well as INSERT statements that illustrate the problem

i mean, i look at your query and it looks okay, and you look at your data and it doesn’t look okay, so i guess i need to see your data and be able to test the query myself

4

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

5

So is my face red yet… I just looked right over that operator pulling my hair out…

Thanks :injured:

6

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.

Glad it is sorted.

7

In your 0_stock_moves table the sum for stock_id C152 and loc_code 10 is 1 correct?
The reorder value for stock_id C152 in 0_loc_stock is 0 correct?

I’ve looked at the data and see that is true.

Thus according to your query above stock_id C152 should be returned because it satisfies your HAVING clause which essentially says:

HAVING SUM(0_stock_moves.qty) > reorder_level

which would be true because 1 > 0

8

what evidence have you provided us that shows the incorrect results that the query is producing?

9

This post did not get any response.

Why?
Should I change something?
Is there a better forum?
Should I hire a coder?

Please let me know what you all think…

Thanks

AM