Skip to Main Content
  • Questions

  • List YYYYMM between two dates from a table using CONNECT BY

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Caga.

Asked: September 03, 2015 - 4:21 pm UTC

Last updated: September 18, 2015 - 3:28 am UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

Hello,

Suppose we have a table that has two columns: a start and stop date.

What would be the best way to list the year||months between the two dates in a single row, using a sub-query to select from this table and preferably using a "CONNECT BY" clause instead of the all_objects table?

I have tried using the all_objects table however the cost of the query was extremely high (besides the fact I was not fully successful in getting the complete result set I had wished for).

ie:
CREATE TABLE TEST1 AS (SELECT SYSDATE AS START_DATE, SYSDATE AS STOP_DATE FROM DUAL WHERE 1 = 2);
INSERT INTO TEST1 (START_DATE, STOP_DATE) VALUES (to_date('01032015','MMDDYYYY'),to_date('01152015','MMDDYYYY') );
INSERT INTO TEST1 (START_DATE, STOP_DATE) VALUES (to_date('04092015','MMDDYYYY'),to_date('06122015','MMDDYYYY') );
INSERT INTO TEST1 (START_DATE, STOP_DATE) VALUES (to_date('10152015','MMDDYYYY'),to_date('03182016','MMDDYYYY') );

In such a case, the output should be:
RESULTSET
201501
201504
201505
201506
201510
201511
201512
201601
201602
201603

Many Thanks,

and Connor said...

I'll concede I'm being a bit lazy by generating all the days between the range (so I dont have to worry too much about month boundaries etc), but this should do the trick

SQL> with
  2    lower_bound as ( select min(start_date) lb from test1 ),
  3    upper_bound as ( select max(stop_date) ub from test1 ),
  4    all_days as
  5      ( select lb+rownum dy
  6        from dual, lower_bound, upper_bound
  7        connect by level <= ub-lb+1
  8      )
  9  select distinct trunc(dy,'MM')
 10  from all_days,
 11       test1
 12  where all_days.dy between test1.start_date and test1.stop_date
 13  order by 1
 14  /

TRUNC(DY,
---------
01-JAN-15
01-APR-15
01-MAY-15
01-JUN-15
01-OCT-15
01-NOV-15
01-DEC-15
01-JAN-16
01-FEB-16
01-MAR-16


Rating

  (2 ratings)

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

Comments

How about Table un-nesting

Rajeshwaran Jeyabal, September 04, 2015 - 8:06 am UTC

Instead of reading the table 'test1' three times (twice for aggregation and once for joins) we scan it once and do a Cartesian of needed rows. 

rajesh@ORA10G> select * from t;

START_DATE  STOP_DATE
----------- -----------
03-jan-2015 15-jan-2015
09-apr-2015 12-jun-2015
15-oct-2015 18-mar-2016

3 rows selected.

rajesh@ORA10G> select *
  2  from t ,
  3     table( cast( multiset( select level
  4    from dual
  5    connect by level <= months_between(trunc(stop_date,'mm'),
  6             trunc(start_date,'mm')) + 1 )
  7      as sys.odcinumberlist) ) ;

START_DATE  STOP_DATE   COLUMN_VALUE
----------- ----------- ------------
03-jan-2015 15-jan-2015            1
09-apr-2015 12-jun-2015            1
09-apr-2015 12-jun-2015            2
09-apr-2015 12-jun-2015            3
15-oct-2015 18-mar-2016            1
15-oct-2015 18-mar-2016            2
15-oct-2015 18-mar-2016            3
15-oct-2015 18-mar-2016            4
15-oct-2015 18-mar-2016            5
15-oct-2015 18-mar-2016            6

10 rows selected.

rajesh@ORA10G> 
rajesh@ORA10G> select  add_months( trunc(start_date,'mm'),column_value-1)
  2  from t ,
  3     table( cast( multiset( select level
  4    from dual
  5    connect by level <= months_between(trunc(stop_date,'mm'),
  6             trunc(start_date,'mm')) + 1 )
  7      as sys.odcinumberlist) ) ;

ADD_MONTHS(
-----------
01-jan-2015
01-apr-2015
01-may-2015
01-jun-2015
01-oct-2015
01-nov-2015
01-dec-2015
01-jan-2016
01-feb-2016
01-mar-2016

10 rows selected.

rajesh@ORA10G>

James Su, September 04, 2015 - 8:21 pm UTC

What if there's overlap between the rows? I assume you want the ranges separately. You will need a key to identify these rows.

CREATE TABLE TEST1 AS (SELECT 1 AS ID,SYSDATE AS START_DATE, SYSDATE AS STOP_DATE FROM DUAL WHERE 1 = 2);
INSERT INTO TEST1 (ID,START_DATE, STOP_DATE) VALUES (1,to_date('01032015','MMDDYYYY'),to_date('01152015','MMDDYYYY') );
INSERT INTO TEST1 (ID,START_DATE, STOP_DATE) VALUES (2,to_date('04092015','MMDDYYYY'),to_date('06122015','MMDDYYYY') );
INSERT INTO TEST1 (ID,START_DATE, STOP_DATE) VALUES (3,to_date('10152015','MMDDYYYY'),to_date('03182016','MMDDYYYY') );

select id,to_char(add_months(start_date,level-1),'yyyymm')
from test1
connect by id=prior id and level<=months_between(trunc(stop_date,'mm'),trunc(start_date,'mm'))+1
and prior sys_guid() is not null;

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.