按凌晨 4 点到凌晨 4 点的值进行分组 SQL Server

Question

我有从 04/01/2012 00:00 到 05/01/2012 05:00 的数据，

假设

StartDate              Value 
04/01/2012 00:00       10 
04/01/2012 05:00       10 
04/01/2012 08:00       10

05/01/2012 01:00       10
05/01/2012 04:00       10
05/01/2012 05:00       10

我按日期进行分组，

SUM(Value)...
GROUP BY YEAR(StartDate), MONTH(StartDate),DAY(StartDate)

该组数据从 04/01/2012 00:00 到 05/01/2012 00： 00 并对全天值求和，即 30

，但我需要按不同时间进行分组

，即04/01/2012 04:00 到 05/01/2012 04:00 这样结果将是 40

如何实现这一点。以前有人这样做过...

Answer 1

SUM(Value)
...
GROUP BY CAST(DATEADD(hour, -4, MyDateCol) AS Date)

Answer 2

select dateadd(hh, 4, date) as date, total from (
    select
        dateadd(dd, datediff(dd, 0, dateadd(hh, -4, startDate)), 0) as date, 
        sum(value) as total
    from yourTable 
    group by dateadd(dd, datediff(dd, 0, dateadd(hh, -4, startDate)), 0)
) t

结果：

date                    total
2012-01-03 04:00:00.000 10
2012-01-04 04:00:00.000 30
2012-01-05 04:00:00.000 20