对于两个日期之间的每个季度，在 SQL SERVER 中按季度添加行 [英] For each quarter between two dates, add rows quarter by quarter in SQL SERVER

问题描述

我有一个表，类型为 int、datetime、datetime:

I have a table, with types int, datetime, datetime:

id    start date    end date
--    ----------    ----------
1     2019-04-02    2020-09-17
2     2019-08-10    2020-08-10

这里是创建/插入:

CREATE TABLE dbo.something
(
  id           int, 
  [start date] datetime, 
  [end date]   datetime
);

INSERT dbo.something(id,[start date],[end date]) 
  VALUES(1,'20190402','20200917'),(2,'20190810','20200810');

什么是可以产生这些结果的 SQL 查询:

What is a SQL query that can produce these results:

id    Year    Quarter
--    ----    ----------
1     2019    2
1     2019    3
1     2019    4
1     2020    1
1     2020    2
1     2020    3
2     2019    3
2     2019    4
2     2020    1
2     2020    2
2     2020    3

推荐答案

如果您不能制作另一个参考表/等，您可以使用 DATEDIFF(和 DATEPART)使用季度，然后进行一些简单的日期运算.

If you cannot make another reference table/etc, you can use DATEDIFF (and DATEPART) using quarters, and then some simple date arithmetic.

下面的逻辑只是查找每个开始日期的第一个季度，然后找到达到最大值的额外季度数.然后执行 SELECT 将额外的季度添加到开始日期，以获得每个季度.

The logic below is simply to find, for each startdate, the first quarter and then the number of additional quarters to get to the maximum. Then do a SELECT where the additional quarters are added to the startdate, to get each quarter.

查询中最难理解的部分是 WITH numberlist 部分 - 所有这些都是生成一系列介于 0 和最大四分之一差之间的整数.如果您已经有一个数字表，则可以改用它.

The hardest part of the query to understand imo is the WITH numberlist section - all this does is generate a series of integers between 0 and the maximum number of quarters difference. If you already have a numbers table, you can use that instead.

关键代码部分在下面，这是一个完整的 DB_Fiddle 以及一些额外的测试数据.

Key code part is below, and here's a full DB_Fiddle with some additional test data.

CREATE TABLE #yourtable (id int, startdate date, enddate date)
INSERT INTO #yourtable (id, startdate, enddate) VALUES
(1, '2019-04-02', '2020-09-17'),
(2, '2019-08-10', '2020-08-20')

; WITH number_list AS
        -- list of ints from 0 to maximum number of quarters
        (SELECT n 
            FROM (SELECT ones.n + 10*tens.n AS n
                    FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
                         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)
                ) AS a
            WHERE n <= (SELECT MAX(DATEDIFF(quarter,startdate,enddate)) FROM #yourtable)
        )
    SELECT  id,
            YEAR(DATEADD(quarter, number_list.n, startdate)) AS [Year],
            DATEPART(quarter, DATEADD(quarter, number_list.n, startdate)) AS [Quarter]
    FROM    (SELECT id, startdate, DATEDIFF(quarter,startdate,enddate) AS num_additional_quarters FROM #yourtable) yt
            CROSS JOIN number_list
    WHERE   number_list.n <= yt.num_additional_quarters 


DROP TABLE #yourtable

这篇关于对于两个日期之间的每个季度，在 SQL SERVER 中按季度添加行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！