Generating Separate Rows For Each Month In A Date Range

Basically I have one row of data, for example: ID - Start_Date - End_Date ------------------------------------ XXA 1/23/14 3/12/14 And I want to create one row for e

Solution 1:

You can use a simple row generation technique by using the CONNECT BY syntax:

with sample_data as 
  (select'XXA' id, to_date('1/23/14','MM/DD/RR') start_date, to_date('3/12/14','MM/DD/RR') end_date from dual)
select id, to_char(add_months(start_date,level -1),'Month YYYY') date_column
from sample_data
connectby level <=extract(monthfrom end_date) -extract(monthfrom start_date) +1;

Edit Adding DISTINCT should allow this to work across rows, I believe, although I would be interested to be proven wrong. Edit 2 Modified example to handle multiple years (should have done that originally). (See example http://sqlfiddle.com/#!4/9eecb/4097/0.)

with sample_data as 
  ( select'XXA' id, to_date('1/23/14','MM/DD/RR') start_date, to_date('3/12/15','MM/DD/RR') end_date from dual unionallselect'XXB' id, to_date('4/12/14','MM/DD/RR') start_date, to_date('6/18/15','MM/DD/RR') end_date from dual )
selectdistinct 
  id,
  to_char(add_months(start_date,level -1),'Month YYYY') date_column,
  add_months(start_date,level -1) sortkey
from sample_data
connectby level <=ceil(months_between(trunc(end_date,'MM'), trunc(start_date,'MM'))) +1orderby id, sortkey;

In my sandbox DB this yields:

IDDATE_COLUMNSORT_COLXXAJanuary2014  23-JAN-201400:00:00XXAFebruary2014  23-FEB-201400:00:00XXAMarch2014  23-MAR-201400:00:00XXBApril2014  12-APR-201400:00:00XXBMay2014  12-MAY-201400:00:00XXBJune2014  12-JUN-201400:00:00

Solution 2:

To get the expected result some year-month data is needed. I'd suggest two possible ways to provide it:

1. create a table or view containing all possible year-months;
2. create a table function which returns required year-months only.

Others options assume implicit generation of year-month data what I think is not as good, as a obvious straight-forward solution.

The 2nd option (in theory) might be faster on bigger input data, however I wouldn't recommend it since you say nothing about performance and it's a little more complicated than the 1st one.

The simplest and easy-to-use solution is:

createtableyear (id intprimary key);
    insertintoyearvalues (2014);
    insertintoyearvalues (2015);
    insertintoyearvalues (2016);


createtablemonth (id intprimary key, name char(255));
    insertintomonthvalues (1, 'Jan');
    insertintomonthvalues (2, 'Feb');
    insertintomonthvalues (3, 'Mar');
    insertintomonthvalues (4, 'Apr');
    insertintomonthvalues (5, 'May');
    insertintomonthvalues (6, 'Jun');
    insertintomonthvalues (7, 'Jul');
    insertintomonthvalues (8, 'Aug');
    insertintomonthvalues (9, 'Sep');
    insertintomonthvalues (10, 'Oct');
    insertintomonthvalues (11, 'Nov');
    insertintomonthvalues (12, 'Dec');


createtable data (id char(15) primary key , start_date date, end_date date);
    insertinto data 
      values ('XXA', to_date('1/23/14','MM/DD/RR'), to_date('3/12/14','MM/DD/RR'));
    insertinto data
      values ('XXB', to_date('4/12/14','MM/DD/RR'), to_date('6/18/14','MM/DD/RR'));


createor replace view calendar asselect y.id*100+ m.id as id, y.id asyear, m.name asmonthfromyear y, month m;


select d.id, c.month, c.year 
    from  calendar c, data d
    where c.id between to_char(d.start_date, 'yyyymm') 
               and to_char(d.end_date, 'yyyymm');

The output is:

XXA|Jan|2014
---+---+----
XXA|Feb|2014
---+---+----
XXA|Mar|2014
---+---+----
XXB|Apr|2014
---+---+----
XXB|May|2014
---+---+----
XXB|Jun|2014

It's important to populate Year table in advance, otherwise it works till 2016.