Break periods at the end of the month

SQL Server 2017

CREATE TABLE [TABLE_1] 
(
    PLAN_NR decimal(28,6) NULL,
    START_DATE datetime NULL,
);

INSERT INTO TABLE_1 (PLAN_NR, START_DATE) 
VALUES (1,'2020-05-01'), (2,'2020-08-01');

CREATE TABLE [TABLE_2] 
(
    PLAN_NR decimal(28,6) NULL,
    PERIOD_NR decimal(28,6) NOT NULL
);
   
INSERT INTO TABLE_2 (PLAN_NR, PERIOD_NR) 
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8),
       (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7), (2, 8);

SQL-FIDDLE-LINK

In TABLE_1 there are plan number and plan start date.

TABLE_2 contains period numbers for each plan number.

I would like to compute the corresponding period start dates:

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.

The Select:

SELECT 
    t1.PLAN_NR, t2.PERIOD_NR, 
    FORMAT(DATEADD (d ,((t2.PERIOD_NR-1)*7) , t1.START_DATE ),'yyyy-MM-dd') START_DATE
FROM
    TABLE_1 t1
JOIN
    TABLE_2 t2 ON t1.PLAN_NR = t2.PLAN_NR
ORDER BY 
    t1.PLAN_NR, t2.PERIOD_NR ASC

This returns the start data but without the extra to consider the respective month end:

+---------+-----------+------------+
| 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-05 |
|       1 |         7 | 2020-06-12 |
|       1 |         8 | 2020-06-19 |
|       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 would like an output 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           |< --- period part before new month
|       1 |         6 | 2020-06-01           |< --- period part after new month
|       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           |< --- period part before new month
|       2 |         5 | 2020-09-01           |< --- period part after new month
|       2 |         6 | 2020-09-02           |
|       2 |         7 | 2020-09-09           |
|       2 |         8 | 2020-09-16           |
+---------+-----------+----------------------+

Answer

SELECT 
    t1.PLAN_NR, t2.PERIOD_NR, 
    --row_number() over() but what if PERIOD_NR is not consecutive?
    t2.PERIOD_NR + SUM(num.n) OVER(PARTITION BY t2.PLAN_NR ORDER BY t2.PERIOD_NR, num.n) AS PERIOD_NR_x,
    FORMAT(CASE WHEN num.n = 1 THEN DATEADD(day, 1, EOMONTH(DATEADD (d ,((t2.PERIOD_NR-1)*7) , t1.START_DATE ))) ELSE DATEADD(d ,((t2.PERIOD_NR-1)*7) , t1.START_DATE ) END, 'yyyy-MM-dd') START_DATE
FROM
    TABLE_1 t1
JOIN
    TABLE_2 t2 ON t1.PLAN_NR = t2.PLAN_NR
CROSS APPLY
(
    SELECT 0 AS n
    UNION ALL
    --new row for month change
    SELECT 1 AS n
    WHERE DATEDIFF(month, DATEADD(d ,(t2.PERIOD_NR-1)*7 , t1.START_DATE), DATEADD(d ,t2.PERIOD_NR*7 , t1.START_DATE)) = 1
) as num    
ORDER BY 
    t1.PLAN_NR, t2.PERIOD_NR ASC

Advertisement

Answer