SQL Server
CREATE TABLE [TABLE_1]
(
PLAN_NR decimal(28,6) NULL,
START_DATE datetime NULL,
MAX_PERIODS decimal(28,6) NULL,
);
INSERT INTO TABLE_1 (PLAN_NR, START_DATE, MAX_PERIODS)
VALUES (1, '2020-05-01', 8),
(2, '2020-08-01', 8);
I've got a table with the columns PLAN_NR, START_DATE and MAX_PERIODS.
Each period is exactly 7 days long, unless the period contains a month end. Then the period should be divided into a range before the end of the month up to and including the last day of the month and a range after the end of the month.
So for the SQL fiddle example the preferred output would look like this:
+---------+-----------+----------------------+
| 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 |
+---------+-----------+----------------------+
I've asked a similar question before but for an Oracle environment and the answer contained a recursive function with a least statement, which does not work in SQL Server.
With a recursive CTE and ROW_NUMBER() window function:
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
See the demo.
Results:
> 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