周报查询

Question

我目前正在编写一个查询，以生成从每个星期一到下一个星期天的每周报告。

SELECT top 10 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday, count(items)
FROM myitemtable
GROUP BY 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday    

vs

SELECT count(items)
FROM myitemtable
WHERE mydatefield >= '2011/05/30 00.00.000' and mydatefield <= '2011/06/05 23.59.59' 

上面的查询有什么问题。在第一次和第二次查询中，计数加起来为一个不同的数字。

Answer 1

在没有特别理解查询的情况下(因为我通常使用比tsql标准得多的SQL变体编写)，我在查看您的查询时的直觉反应是这可能是一个时区问题。但是，你需要做的是验证你得到的答案是你所期望的。为此，请运行以下命令：

SELECT mydatefield,
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
FROM myitemtable
WHERE mydatefield >= '2011/05/30 00.00.000' and mydatefield <= '2011/06/05 23.59.59'

如果数据量太大，这里有另一个选择：

SELECT min(mydatefield),max(mydatefield),count(*),
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
FROM myitemtable
WHERE mydatefield >= '2011/05/30 00.00.000' and mydatefield <= '2011/06/05 23.59.59'
GROUP BY 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday

以及相关的：

SELECT min(mydatefield),max(mydatefield),count(*),
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
FROM myitemtable
GROUP BY 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday

运行这些查询可以帮助您了解"EveryMonday“和"EverySunday”查询是否生成了预期的值。查看最小/最大日期将帮助您了解何时发生不匹配。