扩展多个日期的SQL查询

Question

我有一个包含__DATE__宏的SQL查询。Python脚本将此宏替换为当前日期，然后执行语句，从而给出一天的数据值。

1. 对于选中的第一项，我想用tblLabTestResult.CollectionDate代替__DATE__。
2. 我想包括前7天，而不仅仅是今天。
3. 期望的输出类似于:

Date,Result,Total
2021-08-28,Detected,5
2021-08-28,Not Detected,9
2021-08-29,Detected,23
2021-08-29,Not Detected,6
2021-08-30,Detected,88
2021-08-30,Not Detected,26

当前查询:

SELECT
'__DATE__' as Date,
tblLabTestResult.Result as Result,
Count(tblLabTestResult.Result) as Total
FROM
PncRegDb.dbo.tblLabTestResult as tblLabTestResult
WHERE
tblLabTestResult.TestName like '%cov%'
AND tblLabTestResult.TestName not like '%aoe%'
AND tblLabTestResult.TestName not like '%antibody%'
AND tblLabTestResult.CollectionDate >= '__DATE__'
AND tblLabTestResult.CollectionDate <= '__DATE__ 11:59:59 PM'
GROUP BY
tblLabTestResult.Result;

我如何改变我的SQL查询以适应这些要求?我使用MS SQL Server.

Answer 1

可以使用DATEADD()函数获取7天前的日期，并使用date-7天和date之间的所有日期。我已经更新了以下查询中的条件:

SELECT
'__DATE__' as Date,
tblLabTestResult.Result as Result,
Count(tblLabTestResult.Result) as Total
FROM
PncRegDb.dbo.tblLabTestResult as tblLabTestResult
WHERE
tblLabTestResult.TestName like '%cov%'
AND tblLabTestResult.TestName not like '%aoe%'
AND tblLabTestResult.TestName not like '%antibody%'
AND tblLabTestResult.CollectionDate between DATEADD(day, -7, '__DATE__') and '__DATE__ 11:59:59 PM'
GROUP BY
tblLabTestResult.Result;

Answer 2

几点说明:

• 未聚合的列必须在GROUP BY
你应该把你的日期作为参数传递
• 最好使用半开放间隔来比较日期(不包括终点)，因此@endDate是您想要的日期的第二天
• 使用简短，有意义的别名，使您的代码更具可读性
• 按同一列分组和聚合是没有意义的。如果Result是一个非空列，则Count(Result)与Count(*)相同
• 如果您想按全天分组(CollectionDate有时间组件)，则在SELECT和GROUP BY中用CAST(ltr.CollectionDate AS date)替换ltr.CollectionDate

---

SELECT
ltr.CollectionDate as Date,
ltr.Result as Result,
COUNT(*) as Total
FROM
PncRegDb.dbo.tblLabTestResult as tblLabTestResult
WHERE
ltr.TestName like '%cov%'
AND ltr.TestName not like '%aoe%'
AND ltr.TestName not like '%antibody%'
AND ltr.CollectionDate >= @startdate
AND ltr.CollectionDate < @endDate
GROUP BY
ltr.CollectionDate, ltr.Result;