Oct 08, 2020 at 03:46 PM

# Cumulative Sum and remaining balance based on conditions on previous row in HANA2 SP04

282 Views Last edit Oct 08, 2020 at 05:44 PM 3 rev

I need to derive below columns(marked in yellow) based on Reserve Quantity and Transaction Quantity. Records are Sorted based on Transaction Date and calculations has to be repeated for every material. Here is the Sample Data.

1. Previous Reserve Balance: If first transaction then initialize by setting the number to equal the System 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.

Here is the code that I have implemented currently, I have generated a row number in the ascending order of transaction date for every material. But its having very bad performance, since it processes row by row and there are nearly 40M records.

I tried Map and merge but I don't think that will work for this use case.

It would be great help to me if anyone suggest better way to implement this

```For cur_row as c_uor do
current_Material = CUR_ROW.Material ;

IF current_Material != prev_Material THEN    ## reset all quantities when there is new material
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 row_no partition by material, 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;

## insert each row into a table variable/temp table.
end For;
## finally bulk insert all the records from table variable/temp table into a DB table.

<br>```

Thanks

Attachments

lnetj.png (37.3 kB)