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 |
+--------+----------------+--------------+
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.