sql server 按日期时间截止点 10:00am 进行分组

Question

我正在尝试编写一个 Microsoft SQL Server 2005 查询来计算总值，按日期分组，截止点为上午 10:00？

例如：Table Orders

DateReceived              Total

01-01-2012 06:10:01       2       
01-01-2012 08:10:01       2   
01-01-2012 10:10:01       4   
02-01-2012 08:00:07       4   
02-01-2012 10:00:07       4  

我想计算每日总数，使用上午 10:00 作为截止点，因此上午 10:00 之前的任何订单都会出现在前一天的总数中，上午 10:00 之后的订单会出现在前一天的总数中。当天总计。

我希望看到如下查询结果：

DateReceived              Total

31-12-2011                4       
01-01-2012                8   
02-01-2012                4  

我知道如何仅按 Microsoft SQL Server 中的日期进行分组：

SELECT DISTINCT CONVERT(varchar, [DateReceived], 111) AS [dt_DateReceived], 
SUM([Total]) AS perday
FROM         [Orders] 
GROUP BY CONVERT(varchar, [DateReceived], 111)
ORDER BY [DateReceived] DESC

但是我不确定如何使用 Microsoft SQL Server 添加上午 10:00 的截止时间。

使用 MySQL，我可以通过按减去的间隔进行分组来实现此目的，但是我不确定如何将其转换为 SQL Server：

GROUP BY
  DATE(DATE_SUB( DateReceived , INTERVAL 10 HOUR))

有人可以建议吗？

谢谢你， 杰克

Answer 1

参见翻译：

SELECT 
  CONVERT(varchar, DATEADD(hour, -10, [DateReceived]), 111) AS [dt_DateReceived], 
  SUM([Total]) AS perday
FROM [Orders] 
GROUP BY CONVERT(varchar, DATEADD(hour, -10, [DateReceived]), 111)
ORDER BY 1 DESC

测试脚本

注意我本地的日期时间格式是yyyy-mm-dd

;WITH Orders AS (
    SELECT * FROM (VALUES
        (CAST('2012-01-01 06:10:01' AS DATETIME), 2)
        , ('2012-01-01 08:10:01', 2)
        , ('2012-01-01 10:10:01', 4)
        , ('2012-01-02 08:00:07', 4)
        , ('2012-01-02 10:00:07', 4)
    ) AS Orders (DateReceived, Total)       
)
SELECT  CONVERT(varchar, DATEADD(hour, -10, [DateReceived]), 111) AS [dt_DateReceived]
        , SUM([Total]) AS perday
FROM    [Orders] 
GROUP BY 
        CONVERT(varchar, DATEADD(hour, -10, [DateReceived]), 111)
ORDER BY 
        1

测试脚本可以执行这里

PS：不需要区分