Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
471 views
in Technique[技术] by (71.8m points)

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

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

enter image description here

New System Expectation

enter image description here

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...