Distribute budget over for ranked components in SQL
- by Lee
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
