SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table containers (containerid int, capacity int)
insert into containers values (1, 2), (2, 5), (3, 10);
3 rows affected

create table items (itemid int, containerid int);
insert into items (itemid) values
  (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);
12 rows affected

select containerID, sum(capacity) over (order by containerID)
from containers


containerID
(No column name)


1
2

2
7

3
17


with runningcap as (select containerID, sum(capacity) over (order by containerID) cap
                    from containers)
select i.itemid, min(rc.containerID)
from items i
join runningcap rc
  on rc.cap >= i.itemid
group by i.itemid
order by  i.itemid


itemid
(No column name)


1
1

2
1

3
2

4
2

5
2

6
2

7
2

8
3

9
3

10
3

11
3

12
3