SQL to assign covid patients to hospitals

I have 2 tables:

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)

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.

Advertisement

Answer