How do you subtract from the oldest array first then the second oldest, etc.?

Good day I have three tables - receiving - shipping and stock movement.

everyday i transfer into stock movent the sum of receining with date - havein a stock movent entry per day - I also at end of day update with shipping for that day, in stock table I calculate ne stock level. my problem is that when three days bac i stil have stock i need to start subtracting from oldest entry first till it reaches 0 the move over to second eldest? i have no idea where to start or how to achieve this

The problem isn’t really clear to me. Could you please show the structure with some (sample) data of the tables you’re talking about?

products table

date - product - qty in - qty out - stock
18 - apples - 20 - 8 - 12 ---- new value 2
19 - apples - 20 - 10 - 20 after this the qty stock above should be 2
20 - apples - 10 - 24 - 8 18 stok now 0 19 stock also now 0 as 24 - 2 - 20 leave qty out with 2 then 10 - 2 leaves stock at 8

please see below

If I understand correctly:

18th - 20 in, 8 out, stock is now 12
19th - 20 in, 10 out, stock is now 22
20th - 10 in, 24 out, stock is now 8

Your day end routine could take current stock from the current day, and put it into the “stock brought forward” column for the next day. So it would look a bit like

date brought forward in out stock at end of day
18 0 20 8 12
19 12 20 10 22
20 22 10 12 20

though thanks to @m_hutley for the tables tutorial. Code block indeed did it.

1 Like

Well the obvious question is are you sure that the stock from the 18th are the ones you send out? but let’s assume you are sure of this fact.

The stock value confuses me in your table. EITHER you are storing a total stock of apples, or you’re storing a stock of that-days-apples.
If it’s the first one, then you want subtract the difference from ALL values.
If it’s the second one, then your stock being 20 on the row for 19 makes no sense.

Wait, no, ok, i get it now.

You’re doing a that-days-apples, and you sell the oldest first regardless.

Okay.

So… droop, the logic is actually:

18th - 20 (18) in, 8 (18) out, stock is 12.
19th - 20 (19) in, 10 (18) out, (19) stock is 20. (18) stock is 2.
20th - 10 (20) in, 2 (18) out, 20 (19) out, 2 (20) out. (20) stock is 8, (19) and (18) stock is 0.
2 Likes

Ah, I see. That sounds fiddly, but not terrible, just apply the number required to each days remaining stock until each is emptied. Probably good to maintain an “earliest to send” reference somewhere if the date records go back for some time - and probably essential for perishable goods in any case.

Only way I can think of to do it is pull the entire dataset down… and then something like…

$todayIsold = 20;
foreach($records as $record) {
  $update[] = ['date' => $record['date'], 'product' => $record['product'], 'stock' => max($record['stock'] - $todayIsold,0)];
  $todayIsold -= $record['stock'] - end($update)["stock"];
}

and then push all of the updated stocks back to the database. Ugly, but it’d work.

1 Like

This might be better off using a transactions table, where you keep a record of each apple going in and out and the date, and then you can loop over that in PHP to check the actual stock for each day.

That way you’re not overwriting any history and you can tell for each day how many stock there was, what the flow of stock is, etc.

2 Likes

I have receving ans hipping tables to check that, this is an audit table i am trying to build

how will items sold be done for each product? i have 1200

will it stop when 0? and then loop to the next line?

I agree with @rpkamp. This should be done just like you would do with a bank account logging deposits and withdrawals and then calculate the running balance at any given time or period.