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.
Advertisement
Answer
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>