How to merge consecutive date range Oracle

Please try this query:

select emp_id, lvl, min(date_from) df, max(date_to) dt  from (    select s2.*, rn - sum(marker) over (order by rn) as grp      from (        select s1.*,               row_number() over (order by emp_id, date_from) rn,               case when lag(lvl) over (partition by emp_id order by date_from)                          = lvl                     and lag(date_to) over (partition by emp_id order by date_from) + 1                          = date_from                    then 1                    else 0               end marker          from src_table s1 ) s2 )  group by emp_id, lvl, grp  order by emp_id, min(date_from)

In first subquery S1 I added marker, where 1 is assigned if previous level is corresponding and dates are consecutive. In second subquery this marker is used to build GRP column which has the same values for all matching rows. This column is used in final grouping query to find minimum date_from and maximum date_to. Please run inner queries separately to see what happens in each step. Tested if there are more than two consecutive rows.

Test data and output:

create table src_table (emp_id number(6), lvl varchar2(2), date_from date, date_to date);insert into src_table values (1, 'A', date '2015-07-31', date '2016-03-31');insert into src_table values (1, 'A', date '2016-04-01', date '3000-01-01');insert into src_table values (2, 'A', date '2015-07-31', date '3000-01-01');insert into src_table values (3, 'A', date '2015-05-31', date '2015-12-31');insert into src_table values (3, 'B', date '2016-01-01', date '2016-03-31');insert into src_table values (3, 'A', date '2016-04-01', date '2016-06-30');insert into src_table values (3, 'B', date '2016-07-01', date '3000-01-01');insert into src_table values (4, 'A', date '2015-05-31', date '2015-12-31');insert into src_table values (4, 'A', date '2016-01-01', date '2016-06-30');insert into src_table values (4, 'B', date '2016-07-01', date '3000-01-01'); EMP_ID LVL DF          DT------- --- ----------- -----------      1 A   2015-07-31  3000-01-01      2 A   2015-07-31  3000-01-01      3 A   2015-05-31  2015-12-31      3 B   2016-01-01  2016-03-31      3 A   2016-04-01  2016-06-30      3 B   2016-07-01  3000-01-01      4 A   2015-05-31  2016-06-30      4 B   2016-07-01  3000-01-018 rows selected