时间戳

首页 > 解决方案 > 仅计算每个月的活动记录

sql - 仅计算每个月的活动记录

问题描述

我创建了一个临时的“任务”表，名称#CFTasks如下：

ID           STARTDATE    ENDDATE
1712060019   06/12/2017   07/12/2017
1712060019   07/12/2017   07/12/2017
1712060019   08/12/2017   08/12/2017
1712060019   07/12/2017   08/12/2017
1712060019   07/12/2017   12/12/2017
1712060019   12/12/2017   13/12/2017
1712060019   14/12/2017   14/12/2017
1712060019   07/12/2017   14/12/2017
1712060019   14/12/2017   NULL
1712060019   07/12/2017   14/12/2017
1712060019   18/12/2017   21/12/2017
1712060019   18/12/2017   21/12/2017
1712060019   19/12/2017   21/12/2017
1712060019   02/11/2018   24/09/2018
1712060019   14/09/2018   24/09/2018
1712060019   13/10/2018   05/12/2018
1712060019   13/10/2018   05/12/2018
1712060019   12/11/2018   05/12/2018
1712060019   19/12/2018   07/12/2018
1712060019   13/12/2018   07/12/2018

对于从 2017 年 12 月 1 日到当月的每个月，我想知道这些记录中有多少是活跃的，我的意思[STARTDATE]是 小于或等于该月的第一天，[ENDDATE]大于该月的第一天月或[ENDDATE]为 NULL

IE

[DUEDATE] <= @StartOfMonth AND ( [ENDDATE] >= @StartOfMonth OR [ENDDATE] IS NULL )

对于提供的示例任务表，我希望输出如下所示：

Month        ActiveTasks
01/12/2017   0
01/01/2018   1
01/02/2018   1
01/03/2018   1
01/04/2018   1
01/05/2018   1
01/06/2018   1
01/07/2018   1
01/08/2018   1
01/09/2018   1
01/10/2018   1
01/11/2018   3
01/12/2018   4

我也创建了一个日期表，以便即使在特定报告日期没有活动任务的情况下我也可以得到一个计数，但我无法找到一种方法来连接这两个表以获得所需的结果。

我的日期表是这样创建的：

DROP TABLE IF EXISTS #CalendarMonths
DECLARE @StartDate  date 
SELECT @StartDate = '20171201';

DECLARE @CutoffDate date = getdate()

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(MONTH, @StartDate, @CutoffDate)
),
m(m) AS 
(
  SELECT DATEADD(MONTH, n, @StartDate) FROM seq
)
SELECT m AS [Month]
INTO #CalendarMonths
FROM m
ORDER BY m
OPTION (MAXRECURSION 0);

我已经尝试以我能想到的各种方式加入这两个表，但我总是坚持这样一个事实，即我实际上并不想按[STARTDATE]or[ENDDATE]而是按报告日期和我想要的事实对输出进行分组NULL 从开始日期开始被认为是活动的。

我设法得到的最接近（意味着查询不会简单地失败）如下。问题是我知道我走上了一条死胡同，而且这种方法行不通，无论我如何调整它，但我无法以我的大脑可以在 SQL 中解决它的方式构建问题。在 Excel 中，同样的任务需要我几分钟，但我正在处理超过 600,000 行，而 Excel 并不是适合这项工作的工具。

SELECT cm.[Month],
       COUNT(cft.ID) AS TasksDue
FROM #CalendarMonths cm
LEFT JOIN #CFTasks cft ON cft.STARTDATE <= cm.[Month]
                       AND cft.ENDDATE > cm.[Month]
GROUP BY cm.[Month]
ORDER BY cm.[Month]

标签： sqlsql-serverjoincount

解决方案

如果您需要支持NULL结束日期，那么您只需要调整您的查询：

SELECT cm.[Month],
       COUNT(cft.ID) AS TasksDue
FROM #CalendarMonths cm LEFT JOIN
     #CFTasks cft
     ON cft.STARTDATE <= cm.[Month] AND
        ( cft.ENDDATE > cm.[Month] OR cft.ENDDATE IS NULL )
GROUP BY cm.[Month]
ORDER BY cm.[Month];

推荐阅读