Skip to content
Advertisement

SQL to assign covid patients to hospitals

I have 2 tables:

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

Example:

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:

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement