Tabibitosan Method: Example 2

Grouping consecutive month range without gaps

SQL> create table ex100
  2  as
  3  select date '2013-01-01' dt from dual union all
  4  select date '2013-02-01' from dual union all
  5  select date '2013-03-01' from dual union all
  6  select date '2013-06-01' from dual union all
  7  select date '2013-07-01' from dual union all
  8  select date '2013-10-01' from dual union all
  9  select date '2013-12-01' from dual union all
 10  select date '2014-01-01' from dual
 11  /

Table created.

SQL> select * from ex100;

DT
---------
01-JAN-13
01-FEB-13
01-MAR-13
01-JUN-13
01-JUL-13
01-OCT-13
01-DEC-13
01-JAN-14

8 rows selected.

Expected result:


01-JAN-13 01-MAR-13
01-JUN-13 01-JUL-13
01-OCT-13 01-OCT-13
01-DEC-13 01-JAN-14


ie, group by consective month range without gaps.

SQL> select dt,
  2         extract(year  from dt)*12
  3         +extract(month from dt),
  4      row_number()over (order by dt) rn,
  5      extract(year  from dt)*12
  6         + extract(month from dt)
  7      - row_number()over (order by dt) grp
  8  from ex100;

DT        EXTRACT(YEARFROMDT)*12+EXTRACT(MONTHFROMDT)         RN        GRP
--------- ------------------------------------------- ---------- ----------
01-JAN-13                                       24157          1      24156
01-FEB-13                                       24158          2      24156
01-MAR-13                                       24159          3      24156
01-JUN-13                                       24162          4      24158
01-JUL-13                                       24163          5      24158
01-OCT-13                                       24166          6      24160
01-DEC-13                                       24168          7      24161
01-JAN-14                                       24169          8      24161

8 rows selected.

SQL> select min(dt), max(dt)
  2  from (
  3  select dt,
  4         extract(year  from dt)*12
  5         +extract(month from dt),
  6      row_number()over (order by dt) rn,
  7      extract(year  from dt)*12
  8         + extract(month from dt)
  9      - row_number()over (order by dt) grp
 10  from ex100)
 11  group by grp
 12  order by min(dt);

MIN(DT)       MAX(DT)
---------     ---------
01-JAN-13     01-MAR-13
01-JUN-13     01-JUL-13
01-OCT-13     01-OCT-13
01-DEC-13     01-JAN-14

or,get the count of months from an initial month say '01-01-1900'

SQL> select dt,
  2           months_between(dt, to_date('01/01/1900','dd/mm/yyyy')),
  3           row_number()over (order by dt) rn
  4    from ex100;

DT        MONTHS_BETWEEN(DT,TO_DATE('01/01/1900','DD/MM/YYYY'))             RN
--------- -----------------------------------------------------     ----------
01-JAN-13                                                  1356              1
01-FEB-13                                                  1357              2
01-MAR-13                                                  1358                3
01-JUN-13                                                  1361              4
01-JUL-13                                                  1362               5
01-OCT-13                                                  1365              6
01-DEC-13                                                  1367              7
01-JAN-14                                                  1368               8

8 rows selected.


SQL> select min(dt), max(dt)
  2    from (
  3    select dt,
  4           months_between(dt, to_date('01/01/1900','dd/mm/yyyy'))
  5           -row_number()over (order by dt) grp
  6    from ex100)
  7    group by grp
  8    order by min(dt);

MIN(DT)       MAX(DT)
---------     ---------
01-JAN-13     01-MAR-13
01-JUN-13     01-JUL-13
01-OCT-13     01-OCT-13
01-DEC-13     01-JAN-14