oracle12c - Oracle SQL data migration based on start and end date

Question

Working on migrating old system data to new system.

I need to group the data based on id and name. We need to start date as min date and end date as max date. If any id and name combination contains falls under the same period . We can avoid duplicate and choose lowest to highest date.

Legacy System

New System Expectation

ID - 139247 contains duplicate rows based on name.

Added data in - https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8d6877847c5e052adf703430b5c7f083

Please let me know if more details needed. Thanks in advance.

Answer 1

This is a type of gaps-and-islands problem. Because you want any overlaps, I would go for a cumulative max of the previous enddate to determine where the islands being:

select id, name, min(startdate) as startdate,
       (case when count(enddate) = count(*) then max(enddate)
        end) as enddate
from (select t.*,
             sum(case when prev_enddate >= startdate then 0 else 1 end) over (partition by id, name) as grp
      from (select t.*,
                   max(enddate) over (partition by id, name order by startdate range between unbounded preceding and interval '1' day preceding) as prev_enddate
            from t
           ) t
      ) t
group by id, name, grp
order by name, startdate;

Here is a db<>fiddle.