时间戳

首页 > 解决方案 > 为多条记录生成两个日期之间的日期列表

sql - 为多条记录生成两个日期之间的日期列表

问题描述

我正在尝试编写 SQL 来生成以下数据

Date         Count
2018-09-24   2
2018-09-25   2
2018-09-26   2
2018-09-27   2
2018-09-28   2
2018-09-29   1

我正在使用的基表示例是

ID      StartDate   EndDate
187267  2018-09-24  2018-10-01
187270  2018-09-24  2018-09-30

所以我试图获取两个日期之间的日期列表，然后计算每个日期有多少基本数据记录。

我开始使用临时表并尝试遍历记录以获取结果，但我不确定这是否是正确的方法。

到目前为止我有这个代码

WITH ctedaterange 
     AS (SELECT [Dates] = (select ea.StartWork from EngagementAssignment ea where ea.EngagementAssignmentId IN(SELECT ea.EngagementAssignmentId
                                                                                                            FROM EngagementLevel el INNER JOIN
                                                                                                            EngagementAssignment ea ON el.EngagementLevelID = ea.EngagementLevelId
                                                                                                            WHERE el.JobID = 15072 and ea.AssetId IS NOT NULL))
         UNION ALL
         SELECT [dates] + 1 
         FROM   ctedaterange 
         WHERE  [dates] + 1 < = (select ea.EndWork from EngagementAssignment ea where ea.EngagementAssignmentId IN(SELECT ea.EngagementAssignmentId
                                                                                                            FROM EngagementLevel el INNER JOIN
                                                                                                            EngagementAssignment ea ON el.EngagementLevelID = ea.EngagementLevelId
                                                                                                            WHERE el.JobID = 15072 and ea.AssetId IS NOT NULL)))
SELECT [Dates], Count([Dates])
FROM   ctedaterange 
GROUP BY [Dates]

但我得到这个错误

子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

当我使用的作业仅在 where 子句的子选择中生成一条记录时，我会得到正确的结果，即：

SELECT ea.EngagementAssignmentId
FROM EngagementLevel el INNER JOIN
EngagementAssignment ea ON el.EngagementLevelID = ea.EngagementLevelId
WHERE el.JobID = 15047 and ea.AssetId IS NOT NULL

生成一条记录。

结果如下所示：

Dates                   (No column name)
2018-09-24 02:00:00.000 1
2018-09-25 02:00:00.000 1
2018-09-26 02:00:00.000 1
2018-09-27 02:00:00.000 1
2018-09-28 02:00:00.000 1
2018-09-29 02:00:00.000 1
2018-09-30 02:00:00.000 1
2018-10-01 02:00:00.000 1

标签： sqlsql-server

解决方案

好吧，如果您只有较低的日期范围，则可以使用递归 CTE，如其他答案所示。递归 CTE 的问题在于范围很大，它开始无效 - 所以我想向您展示一种不同的方法，即在不使用递归的情况下构建日历 CTE。

首先，创建并填充示例表（请在以后的问题中保存我们这一步）：

DECLARE @T AS TABLE
(
    ID int,
    StartDate date,
    EndDate date
)
INSERT INTO @T (ID, StartDate, EndDate) VALUES
(187267, '2018-09-24', '2018-10-01'),
(187270, '2018-09-24', '2018-09-30')

然后，在日历 cte 中获取第一个开始日期和所需的日期数：

DECLARE @DateDiff int, @StartDate Date

SELECT @DateDiff = DATEDIFF(DAY, MIN(StartDate), Max(EndDate)),
       @StartDate = MIN(StartDate)
FROM @T

现在，根据row_number（也就是说，除非您已经有一个可以使用的数字（计数）表）构建日历 cte：

;WITH Calendar(TheDate)
AS
(
    SELECT TOP(@DateDiff + 1) DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY @@SPID)-1, @StartDate)
    FROM sys.objects t0
    -- unremark the next row if you don't get enough records...
    -- CROSS JOIN sys.objects t1 
)

请注意，我正在使用row_number() - 1，因此必须选择top(@DateDiff + 1)

最后 - 查询：

SELECT TheDate, COUNT(ID) As NumberOfRecords
FROM Calendar
JOIN @T AS T
    ON Calendar.TheDate >= T.StartDate
    AND Calendar.TheDate <= T.EndDate
GROUP BY TheDate

结果：

TheDate     |   NumberOfRecords
2018-09-24  |   2
2018-09-25  |   2
2018-09-26  |   2
2018-09-27  |   2
2018-09-28  |   2
2018-09-29  |   2
2018-09-30  |   2
2018-10-01  |   1

您可以在 rextester 上看到现场演示。

推荐阅读