CODEWITHSUNDEEP
sqloracle-databaseplsql
user8512043
user8512043

Reputation: 1157

Loop Through and Remove Values Row By Row on Matching Sum

I've a table that has the following structure:

CREATE TABLE HRD.SAMPLE_TEST_2020
(
  ID         VARCHAR2(20 BYTE),
  STOREDATE  DATE,
  VALUE      NUMBER,
  INFO       VARCHAR2(20 BYTE)
)

Sample Data - Below are the sample data

ID      STOREDATE       VALUE   INFO
1122    1/1/2020    2   DONE
1122    1/2/2020    4   DONE
1122    1/3/2020    8   DONE
1122    1/4/2020    10  DONE

The requirement is to delete or remove data that has matching sum of 8. Say id 1122 has value of total 24 and I've to remove 8 out of it. So total remaining would be 16. So final output would be:

ID      STOREDATE       VALUE   INFO
1122    1/1/2020    2   DONE
1122    1/2/2020    4   DONE
1122    1/3/2020    0   
1122    1/4/2020    10  DONE

This may have different scenario say the following:

ID      STOREDATE       VALUE   INFO
1122    1/1/2020    1   DONE
1122    1/2/2020    7   DONE
1122    1/3/2020    13  DONE
1122    1/4/2020    19  DONE

Output:

ID      STOREDATE       VALUE   INFO
1122    1/1/2020    0   
1122    1/2/2020    0   
1122    1/3/2020    13  DONE
1122    1/4/2020    19  DONE

I am not sure in what approach this should be done and tried a simple one that actually doesn't have much impact. Here is the tried one and if this could be improved in any way:

CREATE OR REPLACE procedure ADJUST_VALUE
AS

TotalValue number;

cursor sample_cursor is

SELECT M.EMPNO, SUM(M.VALUE) TOTAL FROM SAMPLE_TEST_2020 m GROUP BY M.EMPNO HAVING SUM(M.VALUE) >= 8;

sample sample_cursor%ROWTYPE;

begin 
open sample_cursor;
  LOOP

     FETCH sample_cursor into sample;
       EXIT WHEN sample_cursor%NOTFOUND;

       TotalValue := sample.TOTAL - 8;
       DBMS_OUTPUT.PUT_LINE(TotalValue);

       UPDATE SAMPLE_TEST_2020 m SET M.VALUE = 0, M.INFO = NULL;

  END LOOP;

close sample_cursor;         
end;

I am open to any approach regarding the procedure using Microsoft SQL Server or Oracle.

Upvotes: 1

Views: 153

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

If I consider your database is MSSQL server version 2012 or newer, you can use LEAD and LAG function as below to achieve your requirement.

Logic: (guess from your sample output)

  1. If current row value is 8 then 0
  2. If current row value + previous row value is 8 then 0
  3. If current row value + next row value is 8 then 0

DEMO HERE

Query is-

SELECT ID,
STOREDATE,
CASE 
    WHEN VALUE = 8 THEN 0
    WHEN VALUE + LAG(VALUE) OVER(ORDER BY STOREDATE) = 8 THEN 0
    WHEN VALUE + LEAD(VALUE) OVER(ORDER BY STOREDATE) = 8 THEN 0
    ELSE VALUE
END VALUE,
INFO
FROM your_table

If I understand correct from your below comment, you wants just one first record where above logic match to be converted to 0. Once one record converted to 0, no other row will be converted to 0 whatever the logic matched or not. If it is correct, the following logic should work for you and VALUE3 will be your final column.

DEMO2 HERE

WITH CTE AS
(
    SELECT ID,
    STOREDATE,
    VALUE,
    CASE 
        WHEN VALUE = 8 THEN 0
        WHEN VALUE + LAG(VALUE) OVER(ORDER BY STOREDATE) = 8 THEN 0
        WHEN VALUE + LEAD(VALUE) OVER(ORDER BY STOREDATE) = 8 THEN 0
        ELSE VALUE
    END VALUE2,
    INFO
    FROM your_table
)

SELECT *,
CASE 
    WHEN 
        (
            SELECT COUNT(*) FROM CTE A 
            WHERE A.VALUE2 = 0 AND A.STOREDATE < B.STOREDATE
        ) >= 1 AND B.VALUE > 7 THEN B.VALUE
    ELSE B.VALUE2
END VALUE3
FROM CTE B

Upvotes: 3

Related Questions