CODEWITHSUNDEEP
oracle-database
Smbdy_sm12345
Smbdy_sm12345

Reputation: 11

Resetting running total and count depending on the condition

I have a question in regards of the running total with reset in the oracle.

Running total for the sum and running count should accumulate for all of the operations made in last 6 days and reset if the conditions: cummulative sum >= 3000 and count >= 2 are met.

That is I have the following table

Sender_id Date SUM
123456 23-01 1000
123456 24-01 2000
123456 25-01 4000
123456 26-01 2000
123456 27-01 3000

and the output should be given in the following format

Sender_id Date SUM Cumm_sum Count
123456 23-01 1000 1000 1
123456 24-01 2000 3000 2
123456 25-01 4000 4000 1
123456 26-01 2000 6000 2
123456 27-01 3000 3000 1

How it can be done?

Upvotes: 1

Views: 126

Answers (1)

MT0
MT0

Reputation: 168041

From Oracle 12, you can use MATCH_RECOGNIZE:

SELECT sender_id,
       "Date",
       sum,
       cumm_sum,
       count
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY sender_id
  ORDER     BY "Date"
  MEASURES
    COUNT(*) AS count,
    SUM(SUM) AS cumm_sum
  ALL ROWS PER MATCH
  PATTERN (data+)
  DEFINE
    data AS COUNT(*) <= 2 OR SUM(SUM) < 3000
);

Which, for the sample data:

CREATE TABLE table_name (Sender_id, "Date", SUM) AS
SELECT 123456, DATE '2021-01-23', 1000 FROM DUAL UNION ALL
SELECT 123456, DATE '2021-01-24', 2000 FROM DUAL UNION ALL
SELECT 123456, DATE '2021-01-25', 4000 FROM DUAL UNION ALL
SELECT 123456, DATE '2021-01-26', 2000 FROM DUAL UNION ALL
SELECT 123456, DATE '2021-01-27', 3000 FROM DUAL;

Outputs:

SENDER_ID Date SUM CUMM_SUM COUNT
123456 23-JAN-21 1000 1000 1
123456 24-JAN-21 2000 3000 2
123456 25-JAN-21 4000 4000 1
123456 26-JAN-21 2000 6000 2
123456 27-JAN-21 3000 3000 1

db<>fiddle here

Upvotes: 1

Related Questions