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.
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.
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
)
;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With