Search code examples
oracleoracle-analytics

Oracle remove rows from a query set based on sum

I have a table with data like below. INVENTORY_ITEM_ID is the unique id for the item, TYPE_QTY is the total quantity for the item in the unique age bucket (AGE_IN_DAYS) and RUNNING_TOTAL is a calculated column based on the TYPE_QTY on age buckets.

enter image description here

Each negative quantity requires to be removed from the final set of rows. For example, at the first occurrence of the negative quantity that is -508, the first rows that has running total that can satisfy the issuance of 508 should be identified and adjusted like below. All the rows above that row should be removed from the result set.

enter image description here

The RUNNING_TOTAL and TYPE_QTY columns are adjusted with the balance from (555-508) and the loop continues. The second issuance of -22 quantity happens against the first row as it has a running total 47 and final results for the given data should be like below

enter image description here

I've made a PL/SQL block that can do the job, however would prefer to achieve it using plain SQL. My current SQL skills are not enough to.

PL/SQL Block

SET SERVEROUTPUT ON;

DECLARE

CURSOR INVDATA IS
SELECT tx.*
from OMSINVDT_TEMP tx
--where inventory_item_id = 35253
order by inventory_item_id,age_in_days desc;

CURSOR inline_data(p_item_id IN NUMBER) IS
SELECT inventory_item_id,
type_qty,
age_in_days,
SUM (type_qty) OVER ( PARTITION BY inventory_item_id ORDER BY age_in_days desc) RUNNING_TOTAL
FROM omsinvdata_temp
where inventory_item_id = p_item_id;

l_line_qty number;
l_last_age_period number := 0;

BEGIN

execute immediate 'truncate table omsinvdata_temp';

for i in invdata loop
--if the qty is greater than 0, add to the temp table
if i.type_qty > 0 then
        insert into omsinvdata_temp(
        inventory_item_id ,
        type_qty ,
        age_in_days ,
        running_total )
        values(i.inventory_item_id, i.type_qty, i.age_in_days, 0);
else
--if the quantity is negative
--open the cursor for the item from temporary table
--and find the row that can satisfy the negative quantity
--dbms_output.put_line('current quantity: '||i.type_qty);
for j in inline_data(i.inventory_item_id) loop
--dbms_output.put_line('Line Qty '||j.type_qty||' Running total: '||j.running_total||' To Issue: '||i.type_qty||' Bucket '||j.age_in_days);


if (abs(i.type_qty)>j.running_total) then
  --  dbms_output.put_line('Running total: '||j.running_total||' not sufficient to issue '||i.type_qty||' Bucket '||j.age_in_days);
    update omsinvdata_temp
    set type_qty =0,
    running_total =0
    where age_in_days = j.age_in_days
    and inventory_item_id = i.inventory_item_id;
    else
  --  dbms_output.put_line('Running total: '||j.running_total||' sufficient to issue '||i.type_qty||' Bucket '||j.age_in_days);
    update omsinvdata_temp
    set type_qty = j.running_total + i.type_qty,
    running_total = j.running_total + i.type_qty
    where age_in_days = j.age_in_days
    and inventory_item_id = i.inventory_item_id;
    exit;
 end if;

end loop;    
end if;
end loop;


commit;


END;

Create table script with shown data

CREATE TABLE "OMSINVDT_TEMP" ("INVENTORY_ITEM_ID" NUMBER, "TYPE_QTY" NUMBER, "AGE_IN_DAYS" NUMBER, "RUNNING_TOTAL" NUMBER)
REM INSERTING into OMSINVDT_TEMP
SET DEFINE OFF;
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (35253,72,6,72);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (35253,384,5,456);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (35253,105,4,561);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (35253,-512,3,49);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (35253,-24,2,25);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (35253,134,1,159);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (266234,2,4,2);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (266234,1,3,3);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (266234,-1,2,2);
Insert into OMSINVDT_TEMP (INVENTORY_ITEM_ID,TYPE_QTY,AGE_IN_DAYS,RUNNING_TOTAL) values (266234,-2,1,0);
commit;

I've already asked this question to Oracle SQL/PLSQL community & failed to explain the construction logic.

https://community.oracle.com/tech/developers/discussion/4480421/sql-match-quantity-and-pick-rows

Solution

  • Here is one way to do it, using only analytic functions and aggregation. You didn't explain the AGE_IN_DAYS column in the output - based on your example, I assume it represents the age of the most recent of the positive rows preceding the last negative row.

    The RUNNING_TOTAL column shouldn't exist in the inputs, since it is calculated from the other data. Even though you have it in the table, I ignore it - I compute it directly. (I assume what you show is not your real starting data, but the point where you weren't able to continue with your solution.)

    There is also a mismatch between the example you used and your INSERT statements. I used the INSERT statements as they are (with a different value for one of the rows); this explains why my output looks different from yours.

    The main trick is in the WITH clause, in the PREP subquery. I assign a flag to the rows after the last "negative" row. Then in the main query I group by this flag, and in addition to that, only when the flag is set, by AGE_IN_DAYS. This way all the rows up to and including the last "negative" one are in one group, while the remaining positive rows are one row per group. (I assume that AGE_IN_DAYS is distinct for each INVENTORY_ITEM_ID; if it isn't, I could use something else, ROWNUM for example - but then the problem wouldn't be well defined anyway.)

    So, here it is. Please review and let me know if you have any questions.

    with prep as (
  select inventory_item_id, type_qty, age_in_days,
         case count(case when type_qty < 0 then 1 end) 
              over (partition by inventory_item_id order by age_in_days)
              when 0 then 'Y' end as past_last_negative
  from   omsinvdt_temp
)
select inventory_item_id, sum(type_qty) as type_qty,
       min(case when type_qty > 0 then age_in_days end) as age_in_days,
       sum(sum(type_qty)) over (partition by inventory_item_id
                                order by max(age_in_days)) as running_total
from   prep
group  by inventory_item_id,
          case past_last_negative when 'Y' then age_in_days end
order  by inventory_item_id, age_in_days desc
;

INVENTORY_ITEM_ID   TYPE_QTY AGE_IN_DAYS RUNNING_TOTAL
----------------- ---------- ----------- -------------
            35253         25           4           159
            35253        134           1           134
           266234          0           3             0