Grouping data by name and date ranges

user_1759823 0
Viewed 384

I have data in my oracle table where I have names and date rages as following:

Name                From        To
Lopes, Janine       07-Jun-17   16-Jul-17
Lopes, Janine       17-Jul-17   23-Jul-17
Lopes, Janine       24-Jul-17   31-Aug-17
Baptista, Maria     23-Dec-16   19-Feb-17
Deyak,Sr, Thomas    22-Jan-17   18-Apr-17
Deyak,Sr, Thomas    27-Apr-17   14-May-17
Deyak,Sr, Thomas    15-May-17   21-May-17
Deyak,Sr, Thomas    22-May-17   28-May-17
Deyak,Sr, Thomas    29-May-17   31-May-17
Serrentino, Joyce   18-Mar-17   30-Apr-17
More, Cathleen      30-Jul-17   13-Aug-17
More, Cathleen      14-Aug-17   20-Aug-17
More, Cathleen      21-Aug-17   27-Aug-17
More, Cathleen      28-Aug-17   03-Sep-17
More, Cathleen      04-Sep-17   10-Sep-17
More, Cathleen      11-Sep-17   24-Sep-17
Barrows, Michael    30-Jan-17   19-Mar-17
Barrows, Michael    20-Mar-17   26-Mar-17
Barrows, Michael    27-Mar-17   02-Apr-17
Barrows, Michael    03-Apr-17   07-Apr-17

Mostly for one user the to date is one greater than from date and is continuous but in some cases there is break the data so my output should look like this: 

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

If I do min(from) and max(to) I loose some records like for Thomas. How should I write sql to get the data is I require.

oracle12c oracle sql database
2 Answers

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_to
from   inputs
match_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
)
;
Admin1

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_to
from   ( 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, gr
order by name, date_fr
;

Output:

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

 7 rows selected
Admin1
Related