0

I have the following dates in a table cp:

start_date: 01/01/2017, end_date: 01/08/2017;
start_date: 01/04/2017, end_date: 01/07/2017

I would like to create a record dynamically for each month between these periods. It tried with the following query, but I don't understand why it's not working.

select add_months(cp.end_date, rownum-1) which_month, id
FROM    (select '1' as id, 
            to_date('01/01/2017', 'DD/MM/YYYY') start_date,
            to_date('01/08/2017', 'DD/MM/YYYY') end_date
    from dual
    UNION
    select  '2' as id, 
            to_date('01/04/2017', 'DD/MM/YYYY') start_date,
            to_date('01/07/2017', 'DD/MM/YYYY') end_date
    from dual) cp, all_objects
WHERE ROWNUM <= months_between(cp.end_date, add_months(cp.start_date, -1));

Can you help me?

Ali Azam
  • 2,047
  • 1
  • 16
  • 25
Moh Za
  • 77
  • 1
  • 8

2 Answers2

0

I don't understand why it's not working.

ROWNUM is a pseudo-column generated by the result set; it doesn't work the way you think.

One solution - there are several but this is the closest there is to an industry standard - is to use the connect by level trick: 

select '1' as id, 
        add_months(date '2017-01-01', (level-1) ) as which_month
from dual
connect by level <= months_between(date '2017-08-01', date '2017-01-01')+1

months_between() takes the arguments (end_date, start_date) - you need to add one to that number to get the end date.

"Would the level-trick still work ?"

Sort of. There's an additional trick necessary to prevent the CONNECT BY generating a product:

select id, 
        add_months(start_date, level-1 ) as which_month
from t23
connect by level <= months_between(end_date , start_date)+1
    --  these two lines required to avoid the need for DISTINCT
    and id = prior id 
    and prior sys_guid() is not null
order by 1, 2
;

LiveSQL demo.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Thank you for your quick response. In your example the period is hardcoded, but what if you have a table containing periods (as the table 'cp' in my query) and would like to create for each month of these period a corresponding record ? Would the level-trick still work ? – Moh Za Dec 21 '17 at 14:39
0

Using the answer of APC, which I would like to thank, I was able to create the following query:

select distinct add_months(cp.start_date, level-1) which_month, id
FROM    (select '1' as id, 
        to_date('01/01/2017', 'DD/MM/YYYY') start_date,
        to_date('01/08/2017', 'DD/MM/YYYY') end_date
    from dual
    UNION
    select  '2' as id, 
        to_date('01/04/2017', 'DD/MM/YYYY') start_date,
        to_date('01/07/2017', 'DD/MM/YYYY') end_date
   from dual) cp
connect by level <= months_between(cp.end_date, cp.start_date) + 1
order by id asc, which_month asc;

I had to use a distinct in the first select statement, but it created for each period in table 'cp' dynamic records for each month (within their respective period).

UPDATE

Using the level-trick, I was able to construct the solution:

select add_months(cp.start_date, level-1) which_month, id
FROM    (select '1' as id, 
        to_date('01/01/2017', 'DD/MM/YYYY') start_date,
        to_date('01/08/2017', 'DD/MM/YYYY') end_date
    from dual
    UNION
    select  '2' as id, 
        to_date('01/04/2017', 'DD/MM/YYYY') start_date,
        to_date('01/07/2017', 'DD/MM/YYYY') end_date
    from dual) cp
connect by level <= months_between(cp.end_date, cp.start_date) + 1
and id = prior id
and prior sys_guid() is not null
order by id asc, which_month asc;
Moh Za
  • 77
  • 1
  • 8