I have 2 tables:
x
CREATE TABLE remdesivir_inventory
(
hospital_id int,
stock int,
state varchar(2)
);
CREATE TABLE remdesivir_requests
(
patient_id int,
prescribed_qty int,
state varchar(2)
);
I want to write a SQL that inserts rows in the remdesivir_assignments table
Every patient whose request can be fulfilled (until the stock runs out) will have a representative row in
the remdesivir_assignments table.
Each patient can be assigned to only 1 hospital (ie. requests cannot be split)
The ‘state’ of the patient and the hospital must match
CREATE TABLE remdesivir_assignments
(
patient_id int,
hospital_id int
);
Example:
INSERT INTO remdesivir_inventory VALUES (1, 200, 'CA');
INSERT INTO remdesivir_inventory VALUES (2, 100, 'FL');
INSERT INTO remdesivir_inventory VALUES (3, 500, 'TX');
INSERT INTO remdesivir_requests VALUES (10, 100, 'CA');
INSERT INTO remdesivir_requests VALUES (20, 200, 'FL');
INSERT INTO remdesivir_requests VALUES (30, 300, 'TX');
INSERT INTO remdesivir_requests VALUES (40, 100, 'AL');
INSERT INTO remdesivir_requests VALUES (50, 200, 'CA');
In this scenario, the following rows will be inserted to the remdesivir_assignments table
(10, 1)
(30, 3)
Advertisement
Answer
You can use a cumulative sum and join
:
select rr.*, ri.hospital_id
from (select rr.*,
sum(prescribed_qty) over (partition by state order by patient_id) as running_pq
from remdesivir_requests rr
) rr join
remdesivir_inventory ri
on ri.state = rr.state and
rr.running_pq <= ri.stock
Here is a db<>fiddle.