Grouping data by name and date ranges

In Oracle 12.1 and above, the MATCH_RECOGNIZE clause does quick work of such requirements. I am using the same setup and simulated data (WITH clause) from my other answer, and the output is also the same.

select name, date_fr, date_tofrom   inputsmatch_recognize(  partition by name  order by date_fr  measures a.date_fr     as date_fr,           last(date_to) as date_to  pattern ( a b* )  define b as date_fr = prev(date_to) + 1);

This can be solved nicely with the Tabibitosan method.

Preparation:

alter session set nls_date_format = 'dd-Mon-rr';Session altered.;

Query (including simulated inputs for convenience):

with     inputs ( name, date_fr, date_to ) as (       select 'Lopes, Janine'    , to_date('07-Jun-17'), to_date('16-Jul-17') from dual union all       select 'Lopes, Janine'    , to_date('17-Jul-17'), to_date('23-Jul-17') from dual union all       select 'Lopes, Janine'    , to_date('24-Jul-17'), to_date('31-Aug-17') from dual union all       select 'Baptista, Maria'  , to_date('23-Dec-16'), to_date('19-Feb-17') from dual union all       select 'Deyak,Sr, Thomas' , to_date('22-Jan-17'), to_date('18-Apr-17') from dual union all       select 'Deyak,Sr, Thomas' , to_date('27-Apr-17'), to_date('14-May-17') from dual union all       select 'Deyak,Sr, Thomas' , to_date('15-May-17'), to_date('21-May-17') from dual union all       select 'Deyak,Sr, Thomas' , to_date('22-May-17'), to_date('28-May-17') from dual union all       select 'Deyak,Sr, Thomas' , to_date('29-May-17'), to_date('31-May-17') from dual union all       select 'Serrentino, Joyce', to_date('18-Mar-17'), to_date('30-Apr-17') from dual union all       select 'More, Cathleen'   , to_date('30-Jul-17'), to_date('13-Aug-17') from dual union all       select 'More, Cathleen'   , to_date('14-Aug-17'), to_date('20-Aug-17') from dual union all       select 'More, Cathleen'   , to_date('21-Aug-17'), to_date('27-Aug-17') from dual union all       select 'More, Cathleen'   , to_date('28-Aug-17'), to_date('03-Sep-17') from dual union all       select 'More, Cathleen'   , to_date('04-Sep-17'), to_date('10-Sep-17') from dual union all       select 'More, Cathleen'   , to_date('11-Sep-17'), to_date('24-Sep-17') from dual union all       select 'Barrows, Michael' , to_date('30-Jan-17'), to_date('19-Mar-17') from dual union all       select 'Barrows, Michael' , to_date('20-Mar-17'), to_date('26-Mar-17') from dual union all       select 'Barrows, Michael' , to_date('27-Mar-17'), to_date('02-Apr-17') from dual union all       select 'Barrows, Michael' , to_date('03-Apr-17'), to_date('07-Apr-17') from dual     )-- End of simulated inputs (for testing only, not part of the solution).-- SQL query begins BELOW THIS LINE. Use your actual table and column names.select name, min(date_fr) as date_fr, max(date_to) as date_tofrom   ( select name, date_fr, date_to,                date_to - sum( date_to - date_fr + 1 ) over (partition by name                                                             order by date_fr) as gr         from   inputs       )group by name, grorder by name, date_fr;

Output:

NAME              DATE_FR   DATE_TO ----------------- --------- ---------Baptista, Maria   23-Dec-16 19-Feb-17Barrows, Michael  30-Jan-17 07-Apr-17Deyak,Sr, Thomas  22-Jan-17 18-Apr-17Deyak,Sr, Thomas  27-Apr-17 31-May-17Lopes, Janine     07-Jun-17 31-Aug-17More, Cathleen    30-Jul-17 24-Sep-17Serrentino, Joyce 18-Mar-17 30-Apr-17 7 rows selected