SQL Question
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.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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
)
;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download