Oracle Database Discussions

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL to eliminate Overlapping records based on beg and end dates

J1604Nov 19 2024 — edited Nov 19 2024

Dear Gurus,

I have Oracle 19 c

I have the data looking like this with data as

(

select 264321502 Cust_id , to_date('01-JAN-17','dd-mon-rr') beg_dt, to_date('31-DEC-17','dd-mon-rr') end_dt, 1 sg_cd from dual union all
select 264321502 , to_date('01-MAY-17','dd-mon-rr'), to_date('31-DEC-17','dd-mon-rr'), 1 from dual union all
select 264321502 , to_date('01-JAN-18','dd-mon-rr'), to_date('31-DEC-18','dd-mon-rr'), 1 from dual union all
select 264321502 , to_date('01-DEC-18','dd-mon-rr'), to_date('31-DEC-19','dd-mon-rr'), 1 from dual union all
select 264321502 , to_date('01-JAN-19','dd-mon-rr'), to_date('31-DEC-20','dd-mon-rr'), 1 from dual union all
select 264321502 , to_date('01-JAN-20','dd-mon-rr'), to_date('31-DEC-21','dd-mon-rr'), 1 from dual union all
select 264321502 , to_date('01-JAN-21','dd-mon-rr'), to_date('31-DEC-22','dd-mon-rr'), 1 from dual union all
select 264321502 , to_date('01-JAN-22','dd-mon-rr'), to_date('31-DEC-23','dd-mon-rr'), 1 from dual union all

select 264321998 , to_date('01-OCT-17','dd-mon-rr'), to_date('30-SEP-18','dd-mon-rr'), 16 from dual union all
select 264321998 , to_date('01-OCT-18','dd-mon-rr'), to_date('30-SEP-19','dd-mon-rr'), 16 from dual union all
select 264321998 , to_date('10-OCT-18','dd-mon-rr'), to_date('30-SEP-19','dd-mon-rr'), 16 from dual union all
select 264321998 , to_date('01-OCT-19','dd-mon-rr'), to_date('30-SEP-20','dd-mon-rr'), 16 from dual union all
select 264321998 , to_date('01-OCT-20','dd-mon-rr'), to_date('30-SEP-21','dd-mon-rr'), 16 from dual

)
select * from data;

I need the output to eliminate overlapping dates for eg. second record and fourth record for cust_id = 264321502 where the begin date is less than previous records end_dt

same way for clinet 264321998 third record where begin_dt = 10-OCT-18 is less than previous records end_dt = 30-sep-19

Output:

output:

264321502 01-JAN-17 31-DEC-17 1
264321502 01-JAN-18 31-DEC-18 1
264321502 01-JAN-19 31-DEC-20 1
264321502 01-JAN-20 31-DEC-21 1
264321502 01-JAN-21 31-DEC-22 1
264321502 01-JAN-22 31-DEC-23 1
264321998 01-OCT-17 30-SEP-18 16
264321998 01-OCT-18 30-SEP-19 16
264321998 01-OCT-19 30-SEP-20 16
264321998 01-OCT-20 30-SEP-21 16

Any help is sincerely appreciated!

Thanks in advance

Added on Nov 19 2024

4 comments

73 views