Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Combine records based on Threshold limit -

S567May 18 2021 — edited May 18 2021

Here is the data of my table -
I Actually need to group the data based on cust and his category and sum his donations till <=27000 and assign it to a value and next keep on summing other payments one after other of same customer under same grouo till his payments reach <= 27000 and then assign it another value. I am looking if it can be achieved without macth_recognize.
With A as
(select '10060' custno, '364162' slipno, 'DEBIT' category,8520 donation from dual union all
select '10060' custno, '364161' slipno, 'DEBIT' category,17680 donation from dual union all
select '10060' custno, '673346' slipno, 'DEBIT' category,23440 donation from dual union all
select '117112' custno, '558190' slipno, 'DEBIT' category,16720 donation from dual union all
select '110291' custno, '681430' slipno, 'DEBIT' category,13597 donation from dual union all
select '110291' custno, '681432' slipno, 'DEBIT' category,9278 donation from dual union all
select '110291' custno, '631094' slipno, 'CREDIT' category,4970 donation from dual union all
select '110291' custno, '627106' slipno, 'CREDIT' category,3378 donation from dual union all
select '117112' custno, '624657' slipno, 'CREDIT' category,17133 donation from dual union all
select '113042' custno, '530736' slipno, 'DEBIT' category,594 donation from dual union all
select '113042' custno, '526144' slipno, 'DEBIT' category,17676 donation from dual union all
select '127182' custno, '433715' slipno, 'DEBIT' category,17712 donation from dual union all
select '127182' custno, '433716' slipno, 'DEBIT' category,5913 donation from dual ;
)
This is sample data

image.png
And expected output -
For example for a customer 10060 it does not matter whether 23440 assigned with 1 and 8520,17680 assigned with 2.
image.png

This post has been answered by Frank Kulash on May 23 2021
Jump to Answer
Comments
Post Details
Added on May 18 2021
20 comments
1,251 views