Min & Max date before disruption

In Oracle, I have this table sample :

+------------+------------+------------+------------+
| EMPLOYEENO | DATE_FROM  |  DATE_TO   |   REASON   |
+------------+------------+------------+------------+
|        101 | 01/08/2019 | 31/08/2019 | SICK LEAVE |
|        101 | 01/09/2019 | 30/09/2019 | SICK LEAVE |
|        101 | 01/10/2019 | 31/10/2019 | SICK LEAVE |
|        101 | 01/11/2019 | 30/11/2019 | SICK LEAVE |
|        101 | 01/12/2019 | 31/12/2019 | SICK LEAVE |
|        101 | 01/01/2020 | 31/01/2020 | SICK LEAVE |
|        101 | 06/07/2020 | 12/07/2020 | SICK LEAVE |
+------------+------------+------------+------------+

What I’d like to have is this :

+------------+------------+------------+------------+
| EMPLOYEENO | DATE_FROM  |  DATE_TO   |   REASON   |
+------------+------------+------------+------------+
|        101 | 01/08/2019 | 31/01/2020 | SICK LEAVE |
|        101 | 06/07/2020 | 12/07/2020 | SICK LEAVE |
+------------+------------+------------+------------+

HR only send me the sick leaves of employees month by month and I don’t really know how to aggregate those dates, so that I could have only 1 record for 1 “real” sick leave, knowing that there would be no disruption between two months. I’ve read about LAG() but I could not find any way to get the job done.

I am not sure ‘disruption’ in my title is the best way to phrase it so please, feel free to edit it/help me to improve it.

Thank you for your time.

Edit : I’m working with Oracle 18C

Answer

You can use the lag and sum windows function with GROUP BY as follows:

SQL> --SAMPLE DATA
SQL> WITH YOUR_TABLE( EMPLOYEENO , DATE_FROM  ,  DATE_TO   ,   REASON) AS
  2  (select 101 , TO_DATE('01/08/2019','DD/MM/YYYY') , TO_DATE('31/08/2019','DD/MM/YYYY') , 'SICK LEAVE' from dual union all
  3  select 101 , TO_DATE('01/09/2019','DD/MM/YYYY') , TO_DATE('30/09/2019','DD/MM/YYYY') , 'SICK LEAVE' from dual union all
  4  select 101 , TO_DATE('01/10/2019','DD/MM/YYYY') , TO_DATE('31/10/2019','DD/MM/YYYY') , 'SICK LEAVE' from dual union all
  5  select 101 , TO_DATE('01/11/2019','DD/MM/YYYY') , TO_DATE('30/11/2019','DD/MM/YYYY') , 'SICK LEAVE' from dual union all
  6  select 101 , TO_DATE('01/12/2019','DD/MM/YYYY') , TO_DATE('31/12/2019','DD/MM/YYYY') , 'SICK LEAVE' from dual union all
  7  select 101 , TO_DATE('01/01/2020','DD/MM/YYYY') , TO_DATE('31/01/2020','DD/MM/YYYY') , 'SICK LEAVE' from dual union all
  8  select 101 , TO_DATE('06/07/2020','DD/MM/YYYY') , TO_DATE('12/07/2020','DD/MM/YYYY') , 'SICK LEAVE' from dual)
  9  --YOUR QUERY STARTS FROM HERE
 10  select EMPLOYEENO, min(date_from) as date_from, max(date_to), REASON
 11  from
 12  (select t.*,
 13         sum(case when lg_dtto is null or lg_dtto <> date_from - 1 then 1 end)
 14             over (partition by EMPLOYEENO, REASON order by date_from) as sm
 15  from
 16  (select t.*,
 17         lag(date_to) over (partition by EMPLOYEENO, REASON order by date_from) as lg_dtto
 18    from your_table t) t
 19  )
 20  group by EMPLOYEENO, sm, REASON;

EMPLOYEENO DATE_FROM MAX(DATE_ REASON
---------- --------- --------- ----------
       101 01-AUG-19 31-JAN-20 SICK LEAVE
       101 06-JUL-20 12-JUL-20 SICK LEAVE

SQL>