Interrupt equal periods at the end of the month

Question

Oracle DB: SQL_FIDDLE

It's about counting and the beginning of weekly periods:

Table_1 lists the plan and it's start date.

Table_2 lists periods and the corrseponding Plan from Table_1.

The select

select 
t2.PERIOD
 ,'WEEK_NUMBER ' || to_char(t1.START_DATE +((t2.PERIOD-1) * 7), 'iw') Week
,to_char(t1.START_DATE + ((t2.PERIOD-1) * 7), 'YYYY-MM-DD') Period_Start
from TABLE_1 t1
join
TABLE_2 t2
on t1.PLAN = t2.PLAN

prints the following:

+--------+----------------+--------------+
| PERIOD |      WEEK      | PERIOD_START |
+--------+----------------+--------------+
|      1 | WEEK_NUMBER 18 | 2020-05-01   |
|      2 | WEEK_NUMBER 19 | 2020-05-08   |
|      3 | WEEK_NUMBER 20 | 2020-05-15   |
|      4 | WEEK_NUMBER 21 | 2020-05-22   |
|      5 | WEEK_NUMBER 22 | 2020-05-29   |
|      6 | WEEK_NUMBER 23 | 2020-06-05   |
|      7 | WEEK_NUMBER 24 | 2020-06-12   |
|      8 | WEEK_NUMBER 25 | 2020-06-19   |
+--------+----------------+--------------+

Question: Each period should have 7 days except for periods with a month break. If a period includes a month break, it should go to the last day of the month, and the rest should be continued as a new period in the next month. The Week Number and Period Number should be adjusted.

Example:

+--------+----------------+--------------+
| PERIOD |      WEEK      | PERIOD_START |
+--------+----------------+--------------+
|      1 | WEEK_NUMBER 18 | 2020-05-01   |
|      2 | WEEK_NUMBER 19 | 2020-05-08   |
|      3 | WEEK_NUMBER 20 | 2020-05-15   |
|      4 | WEEK_NUMBER 21 | 2020-05-22   |
|      5 | WEEK_NUMBER 22 | 2020-05-29   |< --- period part before new month
|      6 | WEEK_NUMBER 23 | 2020-06-01   |< --- period part after new month
|      7 | WEEK_NUMBER 23 | 2020-06-05   |
|      8 | WEEK_NUMBER 24 | 2020-06-12   |
|      9 | WEEK_NUMBER 25 | 2020-06-19   |
+--------+----------------+--------------+

Which procedure is recommended here?

I added another SQL_FIDDLE with adional Periods:

+--------+----------------+--------------+
| PERIOD |      WEEK      | PERIOD_START |
+--------+----------------+--------------+
|      1 | WEEK_NUMBER 23 | 2020-06-01   |
|      2 | WEEK_NUMBER 24 | 2020-06-08   |
|      3 | WEEK_NUMBER 25 | 2020-06-15   |
|      4 | WEEK_NUMBER 26 | 2020-06-22   |
|      5 | WEEK_NUMBER 22 | 2020-05-29   |< --- period part before new month
|      6 | WEEK_NUMBER 23 | 2020-06-01   |< --- period part after new month (Week_Number has changed)
|      6 | WEEK_NUMBER 28 | 2020-07-06   |
|      7 | WEEK_NUMBER 29 | 2020-07-13   |
|      8 | WEEK_NUMBER 30 | 2020-07-20   |
|      9 | WEEK_NUMBER 31 | 2020-07-27   |< --- period part before new month
|     10 | WEEK_NUMBER 31 | 2020-08-01   |< --- period part afternew month (Week_Number has not changed)
|     11 | WEEK_NUMBER 32 | 2020-08-03   |
|     12 | WEEK_NUMBER 33 | 2020-08-10   |
|     13 | WEEK_NUMBER 34 | 2020-08-17   |
|     14 | WEEK_NUMBER 35 | 2020-08-24   |
|     15 | WEEK_NUMBER 36 | 2020-08-31   |< --- period part before new month
|     16 | WEEK_NUMBER 36 | 2020-09-01   |< --- period part before new month (Week_Number has not changed)
|     15 | WEEK_NUMBER 37 | 2020-09-07   | 
|     16 | WEEK_NUMBER 38 | 2020-09-14   |
+--------+----------------+--------------+

Answer 1

You can compute the "period" number with a recursive query, as follows.

As expressed in the comments under your question, I don't quite understand what you mean with the "week" column (and I suspect it's possible that your definition is actually inconsistent - left to you to clarify).

with
  r (plan, period, max_periods, start_date) as (
    select  plan, 1, max_periods, start_date
      from  table_1
    union all
    select  plan, period + 1, max_periods,
            least(start_date + 7, add_months(trunc(start_date, 'mm'), 1))
      from  r
      where period < max_periods
  )
select plan, period, start_date
from   r
;

PLAN     PERIOD START_DATE
---- ---------- ----------
A             1 2020-05-01
A             2 2020-05-08
A             3 2020-05-15
A             4 2020-05-22
A             5 2020-05-29
A             6 2020-06-01
A             7 2020-06-08
A             8 2020-06-15
A             9 2020-06-22
A            10 2020-06-29
A            11 2020-07-01
A            12 2020-07-08

This is based on the max periods from TABLE_1, and I am not using TABLE_2 at all. You can join the two tables after you generate this result set from TABLE_1 alone, or adapt the approach as needed.