I have this table:
item_id | quantity | location_id | stock |
---|---|---|---|
1 | 3 | 11 | 2 |
1 | 3 | 22 | 9 |
i.e the item_id 1, has a total of 3 items, I need to take those 3 items from the stock of the two location (11, 12), it means that the result should be looks like this:
item_id | quantity | location_id | stock | committed |
---|---|---|---|---|
1 | 3 | 11 | 0 | 2 |
1 | 3 | 22 | 8 | 1 |
I've been trying to use window functions like: over and partition by but I'm not sure how to accumulate values, if anyone have an idea how I can solve this I really appreciate thanks!
I've created the following tables and populated them to be consistent with the sample input presented in the original post:
The following SQL performs the operations described in the original post:
The first CTE,
parms
, defines the items and desired quantities. The amount of stock to be taken from each location for each item is calculated incommitments
. The arguments toLEAST
ensure that no more than the available stock is taken.GREATEST
is used to ensure that no stock is taken once the desired quantity has been committed. The stock for each item in each location is adjusted by the committed amount and the updated rows along with the associated committed amounts are returned asupdated_rows
. The finalSELECT
combinesparms
withupdated_rows
to return the output as shown in the original post.This query does not include logic to ensure that adequate stock is available to fulfill the requested quantity.