计算2个日期之间每个日期的记录数 [英] Calculate the number of records for each date between 2 dates

问题描述

我必须创建一个查询来返回多轴图表的结果.我需要计算为 2 个日期之间的每个日期创建的 Id 数量.我试过这个:

I have to create a query to return results for a multi-axis chart. I need to count the number of Ids created for each date between 2 dates. I tried this:

 DECLARE @StartDate datetime2(7) = '11/1/2020',
         @EndDate datetime2(7) = '2/22/2021'

 ;WITH Date_Range_T(d_range) AS 
     (
       SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate) - @StartDate, 0) 
       UNION ALL SELECT DATEADD(DAY, 1, d_range) 
       FROM Date_Range_T 
       WHERE DATEADD(DAY, 1, d_range) < @EndDate
     )

  SELECT d_range, COUNT(Id) as Total 
  FROM Date_Range_T 
       LEFT JOIN [tbl_Support_Requests] on ([tbl_Support_Requests].CreatedDate Between @StartDate AND @EndDate) 
  GROUP BY d_range ORDER BY d_range ASC

当然，问题在于返回错误的 ;WITH

Of course, the problem is with the ;WITH which returns the error

操作数类型冲突:datetime2 与 int 不兼容.

Operand type clash: datetime2 is incompatible with int.

如果我给它从当前日期起的特定天数，则上述方法有效，例如:

The above works if I give it a specific number of days from the current date like:

 ;WITH Date_Range_T(d_range) AS 
 (
   SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 6, 0) 
   UNION ALL SELECT DATEADD(DAY, 1, d_range) 
   FROM Date_Range_T 
   WHERE DATEADD(DAY, 1, d_range) < GETDATE()
 )

哪个返回:

问题是我不知道如何替换日期范围.

The problem is that I cannot figure out how to substitute the date range.

推荐答案

无需重新发明轮子 - 有许多递归 CTE 日历表的示例，类似于以下内容.

No need to reinvent the wheel - there are many examples of recursive CTE calendar tables out there, similar to below.

DECLARE @StartDate date = '01-Nov-2020', @EndDate date = '22-Feb-2021';

WITH Date_Range_T (d_range) AS (
    SELECT @StartDate AS d_range
    UNION ALL
    SELECT DATEADD(DAY, 1, d_range) 
    FROM Date_Range_T 
    WHERE DATEADD(DAY, 1, d_range) < @EndDate
)
SELECT d_range, COUNT(Id) AS Total 
FROM Date_Range_T 
LEFT JOIN tbl_Support_Requests R ON R.CreatedDate = d_range
GROUP BY d_range
ORDER BY d_range ASC
-- Set to the max number of days you require
OPTION (MAXRECURSION 366);

评论:

• 为什么使用 datetime2 作为 date?
• 你确定要<结束日期还是<=?
• 您是否熟悉 between 的工作原理 - 它并不总是很直观.
• 为所有表格设置别名以提高可读性.
• 分号终止所有语句.
• 一致的大小写使查询更易于阅读.
• 对日期字符串使用明确的日期格式.

• Why use a datetime2 for a date?
• Do you definitely want < the end date or <=?
• Are you familiar with how between works - its not always intuitive.
• Alias all tables for better readability.
• Semi-colon terminate all statements.
• Consistent casing makes the query easier to read.
• Use an unambiguous date format for date strings.

这篇关于计算2个日期之间每个日期的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！