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
JP