Tabibitosan Method: Example 7

Get consecutive date range (without any gap) per  group

SQL> create table ex8
  2  ( id number
  3  , dt date);

Table created.

SQL> insert into ex8 values (2, to_date( '01-01-2014','dd-mm-yyyy' ));

1 row created.

SQL> insert into ex8 values (2, to_date( '02-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '03-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '04-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '05-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '06-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '07-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '08-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '09-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '10-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '11-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '12-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (4, to_date( '13-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (5, to_date( '14-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '15-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '16-01-2014','dd-mm-yyyy'));

1 row created.

SQL> commit;

Commit complete.

Expected result:
         2 01-01-2014 03-01-2014
         3 04-01-2014 08-01-2014
         2 09-01-2014 10-01-2014
         3 11-01-2014 12-01-2014
         4 13-01-2014 13-01-2014
         5 14-01-2014 14-01-2014
         2 15-01-2014 16-01-2014




SQL> select id, min(dt), max(dt) from
  2  (
  3  select
  4   id,
  5   dt,
  6   dt - row_number() over (partition by id order by dt,id) grp
  7  from ex8)
  8  group by id,grp
  9  order by min(dt);

        ID MIN(DT)    MAX(DT)
---------- ---------- ----------
         2 01-01-2014 03-01-2014
         3 04-01-2014 08-01-2014
         2 09-01-2014 10-01-2014
         3 11-01-2014 12-01-2014
         4 13-01-2014 13-01-2014
         5 14-01-2014 14-01-2014
         2 15-01-2014 16-01-2014

7 rows selected.