SQL Query where 2 tables "lots" and "orders", value gets carry forwarded to the next row

,
1

-- lots of items
declare @lots table (id varchar(10), qty integer)

-- orders
declare @orders table (num varchar(10), qty integer)

insert @lots (id, qty)
select 'l1', 5
union all select 'l2', 12
union all select 'l3', 8

insert @orders (num, qty)
select 'o1', 2
union all select 'o2', 8
union all select 'o3', 2
union all select 'o4', 5
union all select 'o5', 9
union all select 'o6', 5

-- task - list all pairs of lots-orders. So what lot will be put in what order.
-- example: o1-l1, o2-l1, o2-l2, o2-l3, ....

The order must be fulfilled from lots in lot’s order. Meaning that it will take needed amount from 1st one, then remains from second one, then third…

Here is expected result.

num id QTY taken

o1 l1 2
o2 l1 3
o2 l2 5
o3 l2 2
o4 l2 5
o5 l3 8
o6 NULL NULL

so order o1 is fully fulfilled from l1, then o2 is fulfilled from remained qty from l1 (5-2 = 3), and since 3 is not enough to fulfill o2, it also takes 5 from l2 (8-3 = 5) etc… o2 cannot be fulfilled at all.

2

On approach is a number/tally table to expand the rows. With

https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally

WITH Orders
AS
(
	SELECT O.num
		,ROW_NUMBER() OVER (ORDER BY O.num) AS RowNum
	FROM @orders O
		JOIN dbo.fnTally(1, 500) N
			ON N.N <= O.qty
)
,Lots
AS
(
	SELECT L.id
		,ROW_NUMBER() OVER (ORDER BY L.id) AS RowNum
	FROM @lots L
		JOIN dbo.fnTally(1, 500) N
			ON N.N <= L.qty
)
,Taken
AS
(
	SELECT O.num, L.id
		,COUNT(*) AS QtyTaken
	FROM Orders O
		JOIN Lots L
			ON O.RowNum = L.RowNum
	GROUP BY O.num, L.id
)
SELECT O.num, T.id, T.QtyTaken
	,CASE
		WHEN O.qty = SUM(T.QtyTaken) OVER (PARTITION BY T.num)
		THEN 'Yes'
		ELSE 'No'
	END AS Fulfilled
FROM @orders O
	LEFT JOIN Taken T
		ON O.num = T.num
ORDER BY num, id;