Update SQL column based on the balance carried over from previous rows

Question

I have a table that looks like this:

DocNum	Source	Loc	Bin	Item	Required_SOH	Available_SOH
3	A	ZA	3	ABC	17560	25000
5	A	ZA	3	ABC	5810	NULL
6	A	ZA	3	ABC	3249	NULL
2	B	GB	5	GEF	5000	2000
7	B	GB	5	GEF	200	NULL
8	B	GB	5	GEF	300	NULL

I need to take the Available_SOH from the earliest DocNum and subtract it from the Required_SOH then update the Available_SOH with the balance in the next DocNum that follows. This will be true for each Source, Loc, Bin, Item combination.

So using the example data above, for Source, Loc, Bin, Item combination (A, ZA, 3, ABC), the earliest DocNum is 3 and therefore has an Available_SOH assigned to it. Note all of the earliest DocNums in the table for their respective combination has a SOH value assigned to it that indicates the available stock for that particular item (ABC) in this case.
I need to take the balance of 7440 (Avail_SOH 25000 - Req_SOH 17560) and assign it to Availabe_SOH for DocNum 5 because that's the next DocNum for that combination. Then following the same logic of Avail_SOH 7440 - Req_SOH 5810 = 1630 which will go to Available_SOH for DocNum 6

Therefore the result will look like this:

DocNum	Source	Loc	Bin	Item	Required_SOH	Available_SOH
3	A	ZA	3	ABC	17560	25000
5	A	ZA	3	ABC	5810	7440
6	A	ZA	3	ABC	3249	1630
2	B	GB	5	GEF	5000	2000
7	B	GB	5	GEF	200	-3000
8	B	GB	5	GEF	300	-3200

In short, I need to roll the balance of the stock over to the next DocNum for the Source, Loc, Bin, Item combination. Basically the DocNums that were created first gets the stock fulfilment.

I'm finding it really difficult to articulate what I need but I'm hoping that this makes sense

This is what I tried but It's not giving me the desired result

WITH OrderedData AS (
    SELECT
        DocNum,
        Source,
        Loc,
        Bin,
        Item,
        Required_SOH,
        COALESCE(Available_SOH, 0) AS SOH,
        ROW_NUMBER() OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY DocNum) AS RowNum
    FROM #TempSOH
),
CalculatedSOH AS (
    SELECT
        DocNum,
        Source,
        Loc,
        Bin,
        Item,
        Required_SOH,
        Available_SOH,
        SUM(Available_SOH) OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY RowNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - Required_SOH AS SOH_Calculated
    FROM OrderedData
)
SELECT
    DocNum,
    Source,
    Loc,
    Bin,
    Item,
    Required_SOH,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY DocNum) = 1 THEN SOH_Calculated
        ELSE LAG(SOH_Calculated, 1, 0) OVER (PARTITION BY Source, Loc, Bin, Item ORDER BY DocNum)
    END AS SOH
FROM CalculatedSOH
ORDER BY DocNum;

Answer 1

Available value is calculated as difference between
running total for Availible_SOH
and
running total for Ruquired_SOH without current row - because "available" for current row.

See example

select *
  ,sum(Available_SOH)over(partition by Source, Loc, Bin, Item ORDER BY DocNum) 
   -sum(Required_SOH)over(partition by Source, Loc, Bin, Item ORDER BY DocNum)
   +Required_SOH newAvailable_SOH
from #TempSOH

DocNum	Source	Loc	Bin	Item	Required_SOH	Available_SOH	newAvailable_SOH
3	A	ZA	3	ABC	17560	25000	25000
5	A	ZA	3	ABC	5810	null	7440
6	A	ZA	3	ABC	3249	null	1630
2	B	GB	5	GEF	5000	2000	2000
7	B	GB	5	GEF	200	null	-3000
8	B	GB	5	GEF	300	null	-3200

There expression

  ,sum(Available_SOH)over(partition by Source, Loc, Bin, Item ORDER BY DocNum) 
   -sum(Required_SOH)over(partition by Source, Loc, Bin, Item ORDER BY DocNum)
   +Required_SOH newAvailable_SOH

is equal

  ,sum(Available_SOH)over(partition by Source, Loc, Bin, Item ORDER BY DocNum) 
   -coalesce(sum(Required_SOH)over(partition by Source, Loc, Bin, Item ORDER BY DocNum
                          rows between unbounded preceding and 1 preceding)
            ,0)

fiddle