SQL Query - Conditional Rolling Subtraction and Association

Question

Firstly I'll lay out my data in its simplified form to demonstrate what I'm dealing with:

Stock Table:

Component	Quantity	Batch	Expiry Date
Brick	1500	B1	12/05/2023
Brick	1500	B2	30/05/2023
Brick	1500	B3	22/06/2023
Steel	2500	S1	12/05/2023
Steel	2500	S2	18/05/2023
Glass	15000	G1	29/09/2023
Glass	15000	G2	12/12/2023

Builds Table:

Build ID	Req Component	Req Quantity	Req Date
1	Steel	5000	01/01/2023
1	Brick	4000	02/01/2023
1	Glass	10000	03/01/2023
2	Steel	5000	15/01/2023
2	Brick	4000	16/01/2023
2	Glass	10000	17/01/2023

What you see above is a table of stuck and the batches of said stock and their respective quantities. The next table is a list of upcoming builds and their material requirement.

Based on what I'm trying to achieve I would expect to see the following as the output.

Build ID	Component	Req Quantity	Req Date	Assigned Batch	Rmn Qty(batch)
1	Steel	5000	01/01/2023	S1	2500
1	Steel	2500	01/01/2023	S2	0
1	Brick	4000	02/01/2023	B1	0
1	Brick	2500	02/01/2023	B2	0
1	Brick	1000	02/01/2023	B3	500
1	Glass	10000	03/01/2023	G1	5000
2	Steel	5000	15/01/2023	NO STOCK
2	Brick	4000	16/01/2023	B3	0
2	Brick	3500	16/01/2023	NO STOCK
2	Glass	10000	17/01/2023	G1	0
2	Glass	5000	17/01/2023	G2	10000

The stock is automatically assigned and distributed against the orders based on the required date and the expiry date - the rolling subtraction occurs that a batch may not be fully consumed on a given build and as such can be used again later.

If any more information is required please let me know.