sqlsql-servert-sqlcommon-table-expression

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

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;
Solution

  • 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