By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [TABLE_1] (PLAN_NR INTEGER, START_DATE DATE, MAX_PERIODS INTEGER);
INSERT INTO TABLE_1 (PLAN_NR, START_DATE, MAX_PERIODS) VALUES
(1,'2020-05-01',8),
(2,'2020-08-05',8);
2 rows affected
WITH
rec_cte AS (
SELECT PLAN_NR, START_DATE, MAX_PERIODS,
1 period_nr, DATEADD(day, 7, START_DATE) next_date
FROM TABLE_1
UNION ALL
SELECT PLAN_NR, next_date, MAX_PERIODS,
period_nr + 1, DATEADD(day, 7, next_date)
FROM rec_cte
WHERE period_nr < MAX_PERIODS
),
cte1 AS (
SELECT PLAN_NR, period_nr, START_DATE, MAX_PERIODS
FROM rec_cte
UNION ALL
SELECT PLAN_NR, period_nr, DATEADD(DAY, 1, EOMONTH(next_date, -1)), MAX_PERIODS
FROM rec_cte
WHERE MONTH(START_DATE) <> MONTH(next_date)
),
cte2 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY PLAN_NR ORDER BY START_DATE) rn
FROM cte1
)
SELECT PLAN_NR, rn PERIOD_NR, START_DATE
FROM cte2
WHERE rn <= MAX_PERIODS
ORDER BY PLAN_NR, START_DATE
| PLAN_NR | PERIOD_NR | START_DATE |
|---|---|---|
| 1 | 1 | 2020-05-01 |
| 1 | 2 | 2020-05-08 |
| 1 | 3 | 2020-05-15 |
| 1 | 4 | 2020-05-22 |
| 1 | 5 | 2020-05-29 |
| 1 | 6 | 2020-06-01 |
| 1 | 7 | 2020-06-05 |
| 1 | 8 | 2020-06-12 |
| 2 | 1 | 2020-08-05 |
| 2 | 2 | 2020-08-12 |
| 2 | 3 | 2020-08-19 |
| 2 | 4 | 2020-08-26 |
| 2 | 5 | 2020-09-01 |
| 2 | 6 | 2020-09-02 |
| 2 | 7 | 2020-09-09 |
| 2 | 8 | 2020-09-16 |