计算几乎相等时期的开始日期 [英] Calculate start date of almost equal periods

问题描述

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

我有一个包含 PLAN_NR、START_DATE 和 MAX_PERIODS 列的表格.

I've got a table with the columns PLAN_NR, START_DATE and MAX_PERIODS.

每个期间正好是 7 天，除非期间包含月末.那么这个期间应该被划分为月末之前到月底最后一天的区间和月末之后的区间.

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.

因此对于 SQL 小提琴示例，首选输出如下所示:

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

我在之前问过一个类似的问题 但是对于 Oracle 环境，答案包含带有最少语句的递归函数，这在 SQL Server 中不起作用.

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.

推荐答案

带有递归CTE和ROW_NUMBER()窗口函数:

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

这篇关于计算几乎相等时期的开始日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！