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:
CREATE TABLE items (
id integer PRIMARY KEY
);
CREATE TABLE locations (
id integer PRIMARY KEY
);
CREATE TABLE items_locations (
item_id integer REFERENCES items(id),
location_id integer REFERENCES locations(id),
stock integer,
CONSTRAINT items_locations_pk PRIMARY KEY (item_id, location_id)
);
INSERT INTO items (id)
VALUES (1);
INSERT INTO locations (id)
VALUES (11), (22);
INSERT INTO items_locations (item_id, location_id, stock)
VALUES (1, 11, 2), (1, 22, 9);
The following SQL performs the operations described in the original post:
WITH
parms(item_id, quantity) AS (
VALUES (1, 3)),
commitments AS (
SELECT il.item_id,
il.location_id,
GREATEST(0,
LEAST(il.stock, parms.quantity - SUM(il.stock)
OVER (PARTITION BY il.item_id
ORDER BY il.location_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING))) AS committed
FROM parms
JOIN items_locations il
ON il.item_id = parms.item_id),
updated_rows AS (
UPDATE items_locations il
SET stock = il.stock - c.committed
FROM commitments c
WHERE c.committed > 0
AND il.item_id = c.item_id
AND il.location_id = c.location_id
RETURNING il.*, c.committed)
SELECT ur.item_id, parms.quantity, ur.location_id, ur.stock, ur.committed
FROM parms
JOIN updated_rows ur
ON ur.item_id = parms.item_id
ORDER BY ur.item_id, ur.location_id;
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 in commitments
. The arguments to LEAST
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 as updated_rows
. The final SELECT
combines parms
with updated_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.