Thanks for the question, Rico.

Asked: December 07, 2016 - 11:22 am UTC

Last updated: December 16, 2016 - 1:45 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom

I have the following Case:

CREATE TABLE TEST_DATERANGE
(
TITLE VARCHAR2(10)
,DATEFROM DATE
,DATEUPTO DATE
);
INSERT INTO TEST_DATERANGE VALUES ('Test A',TO_DATE( '01.12.2016' , 'DD.MM.YYYY' ),TO_DATE( '05.12.2016' , 'DD.MM.YYYY' ));
INSERT INTO TEST_DATERANGE VALUES ('Test A',TO_DATE( '02.12.2016' , 'DD.MM.YYYY' ),TO_DATE( '03.12.2016' , 'DD.MM.YYYY' ));
INSERT INTO TEST_DATERANGE VALUES ('Test A',TO_DATE( '06.12.2016' , 'DD.MM.YYYY' ),TO_DATE( '07.12.2016' , 'DD.MM.YYYY' ));
INSERT INTO TEST_DATERANGE VALUES ('Test A',TO_DATE( '08.12.2016' , 'DD.MM.YYYY' ),TO_DATE( '09.12.2016' , 'DD.MM.YYYY' ));
INSERT INTO TEST_DATERANGE VALUES ('Test B',TO_DATE( '10.12.2016' , 'DD.MM.YYYY' ),TO_DATE( '11.12.2016' , 'DD.MM.YYYY' ));
INSERT INTO TEST_DATERANGE VALUES ('Test A',TO_DATE( '13.12.2016' , 'DD.MM.YYYY' ),TO_DATE( '16.12.2016' , 'DD.MM.YYYY' ));

What I need:
-- +-----------------------------------+
-- | TITLE | DATEFROM | DATEUPTO |
-- +-----------------------------------+
-- | Test A | 01.12.2016 | 09.12.2016 |
-- | Test B | 10.12.2016 | 11.12.2016 |
-- | Test A | 13.12.2016 | 16.12.2016 |
-- +-----------------------------------+

I would like to group the values by TITLE and get MIN(DATEFROM) and MAX(DATEEUPTO). But only for continuous date ranges.
I tried it with anlaytic functions lag and lead, but I am not able to get the needed result. Maybe it is not the right way?

Any suggestions?

Thanks a lot
Rico

and Chris said...

Your ranges aren't really continuous. For this to be the case, the previous end date would have to equal the current start date. Not have a gap of a day between them!

Anyway, as you're on 12c, you can do this easily with match_recognize. To do this you want to:

- Split the values up by title
- Sort the rows for each title by end date, then start date

The current row is then part of the previous range if its start date is less than or equal to the previous end date (plus one). i.e.:

rnge as datefrom <= prev(dateupto)+1

So you need to look for a pattern of any row, followed by zero or more rows matching the above condition.

To get the first and last values in the range, you need the min start and max end.

All together this gives you:

alter session set nls_date_format = 'dd Mon';
create table test_daterange (
  title varchar2 ( 10 ) ,datefrom date ,dateupto date
) ;
insert into test_daterange values  (
  'Test A',to_date ( '01.12.2016' , 'DD.MM.YYYY' ) ,to_date ( '05.12.2016' , 'DD.MM.YYYY' )
) ;
insert into test_daterange values (
  'Test A',to_date ( '02.12.2016' , 'DD.MM.YYYY' ) ,to_date ( '03.12.2016' , 'DD.MM.YYYY' )
) ;
insert into test_daterange values  (
  'Test A',to_date ( '06.12.2016' , 'DD.MM.YYYY' ) ,to_date ( '07.12.2016' , 'DD.MM.YYYY' )
) ;
insert into test_daterange values (
  'Test A',to_date ( '08.12.2016' , 'DD.MM.YYYY' ) ,to_date ( '09.12.2016' , 'DD.MM.YYYY' )
) ;
insert into test_daterange values  (
  'Test B',to_date ( '10.12.2016' , 'DD.MM.YYYY' ) ,to_date ( '11.12.2016' , 'DD.MM.YYYY' )
) ;
insert into test_daterange values  (
  'Test A',to_date ( '13.12.2016' , 'DD.MM.YYYY' ) ,to_date ( '16.12.2016' , 'DD.MM.YYYY' )
) ;
  
  
select * from test_daterange
match_recognize (
  partition by title
  order by dateupto, datefrom
  measures 
    min (DATEFROM) as st_dt,
    max (dateupto) as en_dt
  one row per match
  pattern (st rnge*)
  define
    rnge as datefrom <= prev(dateupto)+1
)
order  by st_dt;

TITLE   ST_DT   EN_DT   
Test A  01 Dec  09 Dec  
Test B  10 Dec  11 Dec  
Test A  13 Dec  16 Dec

If you want to know more about working with date ranges, I recommend reading Stew Ashton's blog posts on this:

https://stewashton.wordpress.com/category/date-ranges/

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect

Rico Rauch, December 08, 2016 - 11:08 am UTC

Thank you very much. That fulfills my requirements perfectly

December 08, 2016 - 1:25 pm UTC

Thanks!

Overlapping and containing Ranges

Rico Rauch, December 14, 2016 - 9:59 am UTC

Hi

I now have a case where this solutions doesn't work, when I have multiple overlapping and/or containing DateRanges.

With your link ( https://stewashton.wordpress.com/category/date-ranges/ ) I found a solution, but it only works when the startdate of the actual record is between the range of the previous, but not when it is between the range of the record before the previous.

I sketch an example...

sorted by startdate

|01|02|03|04|05|06|07|08|09|10|11|12|13|14|15|
|--|--|  |  |  |  |  |  |  |  |  |  |  |  |  |
|  |  |--|--|--|--|--|--|--|  |  |  |  |  |  |
|  |  |  |--|--|  |  |  |  |  |  |  |  |  |  |
|  |  |  |  |  |  |--|--|  |  |  |  |  |  |  |
|  |  |  |  |  |  |  |  |  |--|--|  |  |  |  |
|  |  |  |  |  |  |  |  |  |  |--|--|  |  |  |
|  |  |  |  |  |  |  |  |  |  |  |  |  |--|--|

expected result:

|01|02|03|04|05|06|07|08|09|10|11|12|13|14|15|
|--|--|--|--|--|--|--|--|--|--|--|--|  |  |  |
|  |  |  |  |  |  |  |  |  |  |  |  |  |--|--|

Testdata:

CREATE TABLE TEST_DATERANGE
(
    TITLE   VARCHAR2(10)
   ,START_DATE  DATE
   ,END_DATE  DATE
);

insert into test_daterange values ('A',to_date('01','dd'),to_date('02','dd'));
insert into test_daterange values ('A',to_date('03','dd'),to_date('09','dd'));
insert into test_daterange values ('A',to_date('04','dd'),to_date('05','dd'));
insert into test_daterange values ('A',to_date('07','dd'),to_date('08','dd'));
insert into test_daterange values ('A',to_date('10','dd'),to_date('11','dd'));
insert into test_daterange values ('A',to_date('11','dd'),to_date('12','dd'));
insert into test_daterange values ('A',to_date('14','dd'),to_date('15','dd'));

SELECT *
FROM TEST_DATERANGE MATCH_RECOGNIZE(PARTITION BY TITLE
   ORDER BY  START_DATE
   MEASURES MIN(START_DATE) AS START_DATE, MAX(END_DATE) AS END_DATE
   ONE ROW PER MATCH
   PATTERN (VAR_ST VAR_RNGE *)
   DEFINE VAR_RNGE AS START_DATE <= PREV(END_DATE) + 1)
ORDER BY START_DATE;

In the Select-Stmt it only compares to the previous record. In this case it doens't recognize that record 4 is in range of record 2

Do you see a solution for this?

Thank you for your help and kind regards
Rico

December 14, 2016 - 2:41 pm UTC

Here's another way you can do it:

- Generate rows for all the possible dates from the first start to the last end
- Join this to your table where the date are between the start and end and return the distinct dates
- Use the Tabibitosan method to group these:

alter session set nls_date_format = 'DD-MON-YYYY';

with rws as (
  select date'2016-12-01' + rownum - 1 dt from dual
  connect by level <= 15
), vals as (
  select distinct dt from rws
  join   test_daterange
  on     dt between start_date and end_date
), grps as (
  select dt, dt - row_number() over (order by dt) grp
  from   vals
)
  select min(dt), max(dt) from grps
  group  by grp;

MIN(DT)      MAX(DT)      
01-DEC-2016  12-DEC-2016  
14-DEC-2016  15-DEC-2016

For more on Tabibitosan, see this video from Connor:

https://youtu.be/yvimYixXo2Q?t=1m8s

Just MODEL it

Rajeshwaran, Jeyabal, December 15, 2016 - 6:12 am UTC

drop table t purge;
create table t
(   title   varchar2(10),
 start_date  date,
 end_date  date );
insert into t values ('A',to_date('01','dd'),to_date('02','dd'));
insert into t values ('A',to_date('03','dd'),to_date('09','dd'));
insert into t values ('A',to_date('04','dd'),to_date('05','dd'));
insert into t values ('A',to_date('07','dd'),to_date('08','dd'));
insert into t values ('A',to_date('10','dd'),to_date('11','dd'));
insert into t values ('A',to_date('11','dd'),to_date('12','dd'));
insert into t values ('A',to_date('14','dd'),to_date('15','dd'));
commit;

demo@ORA12C> select title, min(start_date) , max(end_date)
  2  from (
  3  select * from t
  4  model
  5    partition by (title)
  6    dimension by ( row_number() over( partition by title
  7                      order by start_date) rn )
  8    measures ( start_date, end_date, 0 grp )
  9    rules (
 10      grp[any] order by rn = case when cv(rn)=1 then 1
 11                                  when cv(rn)>1 and ( end_date[cv()-1]+1 = start_date[cv()] or
 12                                            start_date[cv()] <= max(end_date)[rn < cv(rn)]  or
 13                                            start_date[cv()] = max(end_date)[rn < cv(rn)] +1 )
 14                                  then grp[cv()-1]
 15                                  else grp[cv()-1] +1 end)
 16        )
 17  group by title, grp
 18  order by 1, 2 ;

TITLE      MIN(START_D MAX(END_DAT
---------- ----------- -----------
A          01-DEC-2016 12-DEC-2016
A          14-DEC-2016 15-DEC-2016

2 rows selected.

demo@ORA12C>

December 16, 2016 - 1:45 am UTC

nice stuff

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Perfect

Overlapping and containing Ranges

Just MODEL it

More to Explore

Analytics

Grouping with DateRange

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Perfect

Overlapping and containing Ranges

Just MODEL it

More to Explore

Analytics