0

I have a table ITEM(id, description) and STOCK(id, qty, timestamp, item_id) where item_id is a foreign key mapped to ITEM.

I am presenting the ITEM table with column qty that I get when adding the qty of all the stock of an item.

Now, I need to subtract items. In order to do that, I need to subtract from the qty of stock.

What is the query for this given that the stock with the oldest timestamp should be prioritized to be subtracted and that once the qty reaches 0, the remaining should be subtracted from the other stocks?

P.S.: I'm planning to create a procedure for MySQL so that I'll call it when I need to do that.

sam-s3pi0l
  • 73
  • 1
  • 5
  • This query is one of the general pattern "Select oldest record from table" and it comes up a lot (similar to "select most recent" but it's the same thing with a MIN date instead of a MAX date if you want to look at "select most recent" solutions). Examples: https://stackoverflow.com/questions/10807967/mysql-select-oldest-record-for-each-user https://stackoverflow.com/questions/8125996/how-to-select-oldest-date-from-mysql https://stackoverflow.com/questions/20826830/get-the-oldest-datetime-value-with-mysql https://stackoverflow.com/questions/8084061/select-the-earliest-and-latest-dates – Caius Jard Nov 30 '17 at 09:42
  • @CaiusJard I am able to "select the oldest/newest record from the table" but I'm asking how to subtract from the oldest given that when the thing to subtract reaches zero, the other rows should takeover. – sam-s3pi0l Nov 30 '17 at 09:46
  • If you're writing a procedure, you'll be processing the rows in date descending order, with a cursor, reducing quantity of a row and updating as you go? If you have qty on 3 rows as 10,20,30 and the demand is 45, then 10 and 20 will be 0 and 30 will come to rest at 15 when the cursor has done looping/subtracting qty? Cursors are a mixed blessing https://stackoverflow.com/questions/1745165/looping-over-result-sets-in-mysql - Personally, i'd do it in the front end language, but you did seem to imply it was to be done 100% in MySQL – Caius Jard Nov 30 '17 at 09:57

0 Answers0