计算两个日期之间每个日期的记录数 [英] 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
? - 您确定要
<
结束日期还是< =
? - 您是否熟悉
之间</code>的工作方式-它并不总是直观的.
- 别名所有表以提高可读性.
- 分号终止所有语句.
- 一致的大小写使查询更易于阅读.
- 对日期字符串使用明确的日期格式.
这篇关于计算两个日期之间每个日期的记录数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!