Cumulative Sum and remaining balance based on conditions on previous row in SQL(HANA)

1.4k views Asked by At

I need to derive the columns Previous Reserve Balance, Quantity Allocated, Cumulative Quantity Allocated and Remaining Reserve Balance, which are based on Reserve Quantity and Transaction Quantity. Records are Sorted based on Transaction Date, Transaction Time, and calculations has to be repeated for every material.

(for below data in Google Sheets, see Sample data)

Material  Reserve   Transaction  Transaction  Transaction  Previous  Quantity  Cumulative  Remaining
          Quantity     Date         Time       Quantity    Reserve  Allocated  Quantity   Reserve
                                                           Balance             Allocated  Balance
--------  --------  -----------  -----------  -----------  -------  ---------  ---------  ---------
P1        42        2/4/2018     08:45:03     4            42       4          4          38
P1        42        4/4/2018     12:00:32     11           38       11         15         27
P1        42        6/4/2018     12:00:50     5            27       5          20         22
P1        42        12/4/2018    13:36:25     3            22       3          23         19
P1        42        12/4/2018    12:00:32     -40          19       -23        0          42
P1        42        12/4/2018    12:00:40     10           42       10         10         32
P1        42        14/4/2018    09:06:54     9            32       9          19         23
P1        42        16/4/2018    11:50:06     8            23       8          27         15
P1        42        17/4/2018    11:50:06     15           15       15         42         0
P1        42        19/4/2018    11:50:06     1            0        0          42         0
P1        42        23/4/2018    10:52:54     8            0        0          42         0
P1        42        24/4/2018    10:52:54     2            0        0          42         0
P1        42        25/4/2018    10:52:54     2            0        0          42         0
P1        42        26/4/2018    10:52:54     4            0        0          42         0
  1. Previous Reserve Balance:

    • If first transaction then initialize by setting the number to equal the Reserve Quantity
    • Else carry over the Remaining Reserve Balance from previous transaction
  2. Quantity Allocated

    • If Transaction Quantity is > 0 then use the minimum of Previous Reserve Balance or Transaction Quantity
    • If Transaction Quantity is < 0 then
      • If first transaction then use 0
      • Else use the maximum of Transaction Quantity Or -1*Cumulative Quantity Allocated through previous transaction
  3. Cumulative Quantity Allocated = running total of Quantity Allocated

  4. Remaining Balance = Previous Reserve Balance - Quantity Allocated

This needs to be implemented using HANA SQL(but I believe solution in other DB may also work for HANA DB , so open to solution for any other DB as well). Currently I am able to achieve it by using Cursor in For loop, and storing previous values in variables, but its having performance issue, wondering if there is any better solutions

code snippet:

For cur_row as c_uor do
current_Material = CUR_ROW.Material ;

IF current_Material != prev_Material THEN 
    alloc_qty=0;
    sum_qty=0;
    prev_res_bal=0;
    remain_res_bal=0;
END IF; 
prev_Material =     CUR_ROW.Material;
    if cur_row.ROW_ID = 1 then                        --Row_ID is unique ro number generated as per Key and order asc by transaction date 
        if cur_row.TRANSACTION_QUANTITY < 0 then 
            alloc_qty = 0;
            sum_qty = 0;
            prev_res_bal = (-1)*cur_row."TRANSACTION_QUANTITY";
            remain_res_bal = 0;
        sum_qty = sum_qty+cur_row."TRANSACTION_QUANTITY"; */
        else 
            alloc_qty = cur_row."TRANSACTION_QUANTITY";
            sum_qty = sum_qty+cur_row."TRANSACTION_QUANTITY";
            prev_res_bal = cur_row."SYSTEM_RESERVE_QUANTITY";
            remain_res_bal = prev_res_bal - alloc_qty;
            
        end if;
    else       
            if cur_row.TRANSACTION_QUANTITY < 0 then 
                if cur_row."TRANSACTION_QUANTITY" > sum_qty then
                    alloc_qty = (-1)*sum_qty ;
                    sum_qty = 0;
                    prev_res_bal = remain_res_bal;
                    remain_res_bal = prev_res_bal - alloc_qty;
                else
                    alloc_qty = (-1)*cur_row."TRANSACTION_QUANTITY";
                    --sum_qty   = sum_qty - cur_row."TRANSACTION_QUANTITY";
                    sum_qty = sum_qty + alloc_qty;
                    prev_res_bal = remain_res_bal;
                    remain_res_bal = prev_res_bal - alloc_qty;
                end if;
            elseif remain_res_bal > cur_row."TRANSACTION_QUANTITY" then 
                alloc_qty = cur_row."TRANSACTION_QUANTITY";
                sum_qty = sum_qty+cur_row."TRANSACTION_QUANTITY";
                prev_res_bal = remain_res_bal;
                remain_res_bal = prev_res_bal - alloc_qty;      
            else 
                alloc_qty = remain_res_bal;
                sum_qty = sum_qty+alloc_qty;
                prev_res_bal = remain_res_bal;
                remain_res_bal = prev_res_bal - alloc_qty;
            end if;
 
            
    end if; 
end For;

Thanks

0

There are 0 answers