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
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
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
Cumulative Quantity Allocated = running total of Quantity Allocated
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