Distribute budget over for ranked components in SQL

Question

Assume I have a budget of $10 (any integer) and I want to distribute it over records which have rank field with varying needs. Example:

rank     Req.    Fulfilled?  
1       $3           Y  
2       $4           Y  
3       $2           Y  
4       $3           N  

Those ranks from 1 to 3 should be fulfilled because they are within budget. whereas, the one ranked 4 should not.

I want an SQL query to solve that.

Below is my initial script:

CREATE TABLE budget (
id VARCHAR (32),
budget INTEGER,
PRIMARY KEY (id));

CREATE TABLE component (
id VARCHAR (32),
rank INTEGER,
req INTEGER,
satisfied BOOLEAN,
PRIMARY KEY (id));

INSERT INTO budget (id,budget) VALUES ('1',10);
INSERT INTO component (id,rank,req) VALUES ('1',1,3);
INSERT INTO component (id,rank,req) VALUES ('2',2,4);
INSERT INTO component (id,rank,req) VALUES ('3',3,2);
INSERT INTO component (id,rank,req) VALUES ('4',4,3);

Thanks in advance for your help.

Lee

Answer 1

Well, the example you gave is fairly easy:

select rank, req,
       sum(req) over(order by rank) < (select budget from budget where id = '1')
             as fulfilled
from component

But this doesn't take into account:

• there are 2 units left over from the budget that could be allocated to a further component with a lower requirement
• budget is allocated to components rank-first, not sure that's what you meant

So if there was a component (id=5, rank=5, req=2) and that should be fulfilled, this isn't sufficient.

TBH I suspect a function to do the allocation is the best bet- should be quite easy to simply run through the result of a query ordered by "rank asc" and update the fulfilled column according to the current state.