How to generate weeks in a given range of time

I have a table in which is stored such information:

id     begin_date      end_date
1      01.01.2023      27.01.2023
2      15.09.2023      30.09.2022

I am trying to write a SQL statement which will generate every week in this range of time,

for example the output should be:

id     week_from_till
1      26.12.2022 -  01.01.2023
1      02.01.2023 -  08.01.2023
1      09.01.2023 -  15.01.2023
1      16.01.2023 -  22.01.2023 
1      23.01.2023 -  29.01.2023

2      12.09.2022 -  18.09.2022    
2      19.09.2022 -  25.09.2022
2      26.09.2022 -  02.10.2022

>Solution :

Here’s one option.

Sample data:

SQL> with test (id, begin_date, end_date) as
  2    (select 1, date '2023-01-01', date '2023-01-27' from dual union all
  3     select 2, date '2022-09-15', date '2022-09-30' from dual
  4    )

Query:

  5  select id,
  6    trunc(begin_date, 'iw') + (column_value - 1) * 7     week_from,
  7    trunc(begin_date, 'iw') + (column_value - 1) * 7 + 6 week_to
  8  from test cross join
  9    table(cast(multiset(select level from dual
 10                        connect by level <= (trunc(end_date, 'iw') + 7
 11                                           - trunc(begin_date, 'iw')) / 7
 12                       ) as sys.odcinumberlist))
 13  order by 1, 2;

        ID WEEK_FROM  WEEK_TO
---------- ---------- ----------
         1 26.12.2022 01.01.2023
         1 02.01.2023 08.01.2023
         1 09.01.2023 15.01.2023
         1 16.01.2023 22.01.2023
         1 23.01.2023 29.01.2023
         2 12.09.2022 18.09.2022
         2 19.09.2022 25.09.2022
         2 26.09.2022 02.10.2022

8 rows selected.

SQL>