I'm trying to write a SQL query that will do a few things, but i'll start with the easiest first:
I have a group of objects with IDs (in this example, different colors of marbles). My query will SUM these marbles and result in a table like this:
| marbles | number_of_marbles |
|---|---|
| Green Marble | 50 |
Then, I have a table of baskets where I want to drop these marbles into. The quantity in this table represents how many are needed to satisfy a full basket of marbles.
| basket | required_marbles | marble_type |
|---|---|---|
| basket_one | 60 | Green Marble |
| basket_two | 30 | Green Marble |
The end result is that I want to prioritize filling a basket with the least amount of required marbles. The reason is if I can fill up a basket, I can set aside and remove from table eventually.
The desired output in this case would be:
| basket | required_marbles | marble_type |
|---|---|---|
| basket_one | 40 | Green Marble |
| basket_two | 0 | Green Marble |
I used a CTE provided by a previous answer but ran into a slight issue:
select top 1 @number_of_marbles = number_of_marbles from @marbles
;with cte as (select *,ROW_NUMBER() over(order by [required_marbles] asc
) as rno from @required_marbles_table),
cte1(rno,tot) as (select top 1 rno, @number_of_marbles-required_marbles as tot from cte t
union all select t.rno, tot-required_marbles as tot from cte t,cte1 where (t.rno-1)=cte1.rno
)
update t set required_marbles=(case when tot>0 then 0 else -tot end) from cte t inner join cte1 on t.rno=cte1.rno
select * from @required_marbles_table
In this case, the result is correct (i.e. look at desired output table). However, if the quantity wouldn't 0 out a row (i.e. if number_of_marbles is actually 5 for example), the number is subtracted from every single row.
| basket | required_marbles | marble_type |
|---|---|---|
| basket_one | 55 | Green Marble |
| basket_two | 25 | Green Marble |
That's the 'easy' part
The other piece I would like to add is, if you have two different types of marbles.
| marbles | number_of_marbles |
|---|---|
| Green Marble | 50 |
| Blue Marble | 30 |
and I want to fill up some baskets:
| basket | required_marbles | marble_type |
|---|---|---|
| basket_one | 60 | Green Marble |
| basket_two | 30 | Green Marble |
| basket_three | 50 | Blue Marble |
I would like to be able to use the 'new' code to loop through based off marble color and allocate to it's respective basekt. End result being something like:
| basket | required_marbles | marble_type |
|---|---|---|
| basket_one | 40 | Green Marble |
| basket_two | 0 | Green Marble |
| basket_three | 20 | Blue Marble |
I do not use your environment, but as this question is marked with SQL tag I made two solutions in Oracle. They are close to standard SQL, so can be easily adapted to your needs, I hope. First solution uses only analytic sum and some logic with greatest and least:
select basket, type, req, amt,
least(greatest(
sum(req) over (partition by type order by req, basket) - amt,
0), req) result
from baskets join marbles using(type) order by type desc, basket
Second solution uses recursive query:
with base as (
select basket, type, req, amt,
row_number() over (partition by type order by req, basket) rn
from baskets b join marbles using (type) ),
r(rn, basket, type, req, amt) as (
select rn, basket, type, greatest(req - amt, 0),
greatest(amt - req, 0)
from base where rn = 1
union all
select r.rn + 1, b.basket, b.type, greatest(b.req - r.amt, 0),
greatest(r.amt - b.req, 0)
from r join base b on b.rn = r.rn + 1 and b.type = r.type )
select basket, type, req from r order by type desc, basket;