Calculate start date of almost equal periods

Question

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);

SQL - FIDDLE

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.

Why do you start from `'2020-08-05'` your expected results for PLAN_NR = 2? Shouldn't it be `'2020-08-01'`? — forpas, Jan 02 '21 at 15:51
yeah u are right sorry .. I wwanted the second plan to start from 05.08.2020 — Peter S, Jan 02 '21 at 15:58
Sorry for that. I have now been able to test both answers more extensively and find both approaches very good. The tally table access is significantly faster in my actual application but that has nothing to do with my actual question, so I have accepted your answer again. — Peter S, Jan 05 '21 at 12:15

Thom A · Answer 1 · 2021-01-02T15:33:51.137

I would personally use a Tally; for large datasets they are significantly faster (especially if you need to also recurse a lot). If the numbers are pretty low, I.e. 10 or lower, then you can just do this in the FROM. Then you can use a CASE and LEAD to check if the start and end dates are the same to generate the extra row for a new month starting:

WITH Dates AS(
    SELECT T1.PLAN_NR,
           V.I+1 AS PERIOD_NR,
           DATEADD(DAY, 7*V.I, T1.START_DATE) AS START_DATE,
           LEAD(DATEADD(DAY, 7*V.I, T1.START_DATE)) OVER (PARTITION BY PLAN_NR ORDER BY V.I) AS END_DATE
    FROM dbo.TABLE_1 T1
         JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) V(I) ON MAX_PERIODS >= V.I)
SELECT D.PLAN_NR,
       D.PERIOD_NR,
       V.START_DATE
FROM Dates D
     CROSS APPLY (VALUES(START_DATE),(CASE WHEN MONTH(START_DATE) != MONTH(END_DATE) THEN DATEADD(MONTH,DATEDIFF(MONTH,0,END_DATE),0) END)) V(START_DATE)
WHERE V.START_DATE IS NOT NULL;

db<>fiddle

If it's larger than 10, like way larger, then you can use an inline one, using CTEs (see Tally Tables in T-SQL).

Thanks. But how to divide a period that includes an end of month. — Peter S, Jan 02 '21 at 15:16

forpas · Accepted Answer · 2021-01-02T16:10:50.317

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

score 0 · Answer 3 · answered Jan 02 '21 at 15:29

You can try recursive queries for that. I would separately generate regular days (every week) and every first of month and then union them (note that we will use union instead union all as it will exclude duplicates). Please, see below query:

with cte as (
  select datefromparts(2020, 5, 1) dt
  union all
  select dateadd(dd, 7, dt) from cte
  where dt < datefromparts(2020, 9, 16)
), firstDays as (
  select datefromparts(2020, 5, 1) firstDay
  union all
  select dateadd(m, 1, firstDay) from firstDays
  where firstDay < datefromparts(2020, 8, 2)
)
select firstDay from firstDays
union
select dt from cte;

SQL fiddle

Calculate start date of almost equal periods

3 Answers3

Linked