CODEWITHSUNDEEP
sqlsql-servert-sqldatetimesql-server-2017
Peter S
Peter S

Reputation: 575

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

Upvotes: 0

Views: 147

Answers (2)

lptr
lptr

Reputation: 6788

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

Upvotes: 0

Mr.P
Mr.P

Reputation: 1257

Use window functions (LEAD / LAG ) to get the start and end of the period ...

    SELECT t1.PLAN_NR
    , t2.PERIOD_NR
    , FORMAT(DATEADD (d ,((t2.PERIOD_NR-1)*7) , t1.START_DATE ),'yyyy-MM-dd') START_DATE
    , CASE
       WHEN 
        lead(
          FORMAT(DATEADD (d ,((t2.PERIOD_NR-1)*7) , t1.START_DATE ),'yyyy-MM-dd')
          ) over (partition by 
                    FORMAT(DATEADD (d ,((t2.PERIOD_NR-1)*7) , t1.START_DATE ),'yyyy-MM')
                  order by t2.period_nr)
         IS NULL THEN '< --- period part before new month'
       WHEN lag(
          FORMAT(DATEADD (d ,((t2.PERIOD_NR-1)*7) , t1.START_DATE ),'yyyy-MM-dd')
          ) over (partition by 
                    FORMAT(DATEADD (d ,((t2.PERIOD_NR-1)*7) , t1.START_DATE ),'yyyy-MM')
                  order by t2.period_nr)
         IS NULL THEN '< --- period part after new month'
       END as period_break
    from TABLE_1 t1
    join TABLE_2 t2
    on t1.PLAN_NR = t2.PLAN_NR
    order by t1.PLAN_NR, t2.PERIOD_NR asc

SQL Fiddle

PLAN_NR PERIOD_NR   START_DATE  period_break
1       1           2020-05-01  < --- period part after new month
1       2           2020-05-08  (null)
1       3           2020-05-15  (null)
1       4           2020-05-22  (null)
1       5           2020-05-29  < --- period part before new month
1       6           2020-06-05  < --- period part after new month
1       7           2020-06-12  (null)
1       8           2020-06-19  < --- period part before new month
2       1           2020-08-01  < --- period part after new month
2       2           2020-08-08  (null)
2       3           2020-08-15  (null)
2       4           2020-08-22  (null)
2       5           2020-08-29  < --- period part before new month
2       6           2020-09-05  < --- period part after new month
2       7           2020-09-12  (null)
2       8           2020-09-19  < --- period part before new month

Upvotes: 1

Related Questions