I'm not sure if the following can be done using a mere select statement, but I have two tables (truncated with the data necessary to the problem).

Inventory Item

  • id int (PRIMARY)
  • quantity int

Stock - Contains changes in the stock of the inventory item (stock history)

  • id int (PRIMARY)
  • inventory_item_id int (FOREIGN KEY)
  • quantity int
  • created datetime

The quantity in stock is the change in stock, while the quantity in inventory item is the current quantity of that item

EVERYTHING IN THE running COLUMN WILL RETURN 0

SELECT
inventory_item.id,
(inventory_item.quantity - SUM(stock.quantity)) AS running
FROM
    stock
        JOIN
    inventory_item ON stock.inventory_item_id = inventory_item.id
GROUP BY inventory_item.id

THE QUESTION

Now, what I would like to know is: Is it possible to select all of the dates in the stock table where the running quantity of the inventory_item ever becomes zero using a SELECT?

I know this can be done programmatically by simply selecting all of the stock data in one item, and subtracting the stock quantity individually from the current inventory item quantity, which will get the quantity before the change in stock happened. Can I do this with a SELECT?

mysql
Edit
Report