Generate a range of records depending on from-to dates

Question

I have a table of records like this:

Item	From	To
A	2018-01-03	2018-03-16
B	2021-05-25	2021-11-10

The output of select should look like:

Item	Month	Year
A	01	2018
A	02	2018
A	03	2018
B	05	2021
B	06	2021
B	07	2021
B	08	2021

Also the range should not exceed the current month. In example above we are asuming current day is 2021-08-01.

I am trying to do something similar to THIS with CONNECT BY LEVEL but as soon as I also select my table next to dual and try to order the records the selection never completes. I also have to join few other tables to the selection but I don't think that would make a difference.

I would very much appreciate your help.

score 2 · Accepted Answer · answered Aug 01 '21 at 08:49

Row generator it is, but not as you did it; most probably you're missing lines #11 - 16 in my query (or their alternative).

SQL> with test (item, date_from, date_to) as
  2    -- sample data
  3    (select 'A', date '2018-01-03', date '2018-03-16' from dual union all
  4     select 'B', date '2021-05-25', date '2021-11-10' from dual
  5    )
  6  -- query that returns desired result
  7  select item,
  8         extract(month from (add_months(date_from, column_value - 1))) month,
  9         extract(year  from (add_months(date_from, column_value - 1))) year
 10  from test cross join
 11    table(cast(multiset
 12      (select level
 13       from dual
 14       connect by level <=
 15         months_between(trunc(least(sysdate, date_to), 'mm'), trunc(date_from, 'mm')) + 1
 16      ) as sys.odcinumberlist))
 17  order by item, year, month;

ITEM       MONTH       YEAR
----- ---------- ----------
A              1       2018
A              2       2018
A              3       2018
B              5       2021
B              6       2021
B              7       2021
B              8       2021

7 rows selected.

SQL>

In Oracle 12c and above there's a lateral join, which allows correlated datasource without additional wrapper — astentx, Aug 01 '21 at 10:55

score 1 · Answer 2 · answered Aug 01 '21 at 12:37

Recursive CTEs are the standard SQL approach to this type of problem. In Oracle, this looks like:

with cte(item, fromd, tod) as (
      select item, fromd, tod
      from t
      union all
      select item, add_months(fromd, 1), tod
      from cte
      where add_months(fromd, 1) < last_day(tod)
     )
select item, extract(year from fromd) as year, extract(month from fromd) as month
from cte
order by item, fromd;

Here is a db<>fiddle.

Generate a range of records depending on from-to dates

2 Answers2