Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Grouping data by name and date ranges

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.

like image 869
Auguster Avatar asked Oct 02 '17 15:10

Auguster


People also ask

How do I group data values in Excel?

Select Home > Group by. In the Group by dialog box, select Advanced to select more than one column to group by. To add another column, select Add Grouping.


1 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
)
;
like image 168
mathguy Avatar answered Sep 20 '22 18:09

mathguy