sql - 周四到周三的每周数据Sql查询

Question

通过使用下面的链接，我试图进行每周一次的查询。我的一周从星期四开始，一直持续到星期三，我正确地更改了这一周，但 (Field1) 的总和没有正确显示。我做错了什么？

Query for weekly report

SELECT 
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104') AS EveryThrusday,
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME)+1, '19000104') - 1 AS EveryWednesday,
    SUM(Field1)
FROM 
    Table1
GROUP BY 
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104'),  
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME)+1, '19000104')-1
ORDER BY
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104'),  
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME)+1, '19000104')-1

表 1:

Date        field1
1-May-14    102948.66
2-May-14    102927.21
3-May-14    102925.89
4-May-14    102427.55
5-May-14    101551.95
6-May-14    102952.12
7-May-14    102947.53

总计 = 718680.93

表 2:

Start date  End date    field1
1-May-14    7-May-14    719793.72

在第一个表中，我每天检索从 5 月 1 日星期四到 5 月 7 日的数据。那几天的总和是718680.93

但是如果我运行每周查询，会发现字段 1 表 2 中的值不同

Answer 1

通过查看每个日期的 DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104') 结果，您可以很容易地看出总和与正确值不同的原因在你的范围内。以下查询:

SELECT
  START_DATETIME,
  Thursday = DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104')
FROM dbo.Table1;

会给你这样的结果:

START_DATETIME  Thursday
--------------  ----------
2014-05-01      2014-05-01
2014-05-02      2014-05-01
2014-05-03      2014-05-01
2014-05-04      2014-05-08
2014-05-05      2014-05-08
2014-05-06      2014-05-08
2014-05-07      2014-05-08

如您所见，对于从星期四开始的同一周内的所有日期，您的表达式不会返回相同的结果。结果，您的查询将不同的日期分组，这反过来又是您得到错误结果的原因。

请注意，无论 DATEFIRST 设置如何，表达式都会返回相同的不一致日期，因为DATEDIFF does not respect that setting并且始终按照 DATEFIRST 设置为 7 的方式计算结果。

要解决此问题，您可以将 DATEDIFF(WEEK) 替换为 DATEDIFF(DAY)/7:

DATEADD(WEEK, DATEDIFF(DAY, '19000104', START_DATETIME) / 7, '19000104')

由于两个操作数都是整数，SQL Server 将执行整数除法，因此其结果将是整数(在本例中是正确的)周数。

或者，您可以通过从日期中减去星期几来计算一周的开始:

DATEADD(DAY, 1 - DATEPART(WEEKDAY, START_DATETIME), START_DATETIME)

同样，周末将使用 7 - DATEPART(...)。以上假定 DATEFIRST 设置为 4(即星期四)。如果您的环境不同，您需要相应地调整 DATEADD 的第二个参数。