我试图在一天内平均sql Server 2005中的数据.如果我使用简单查询,这是我的数据库看起来像这样
数据：
- timestamp Feed
- 02/07/2011 12:00:01 1246
- 02/07/2011 12:00:01 1234
- 02/07/2011 12:00:01 1387
- 02/07/2011 12:00:02 1425
- 02/07/2011 12:00:03 1263
- ...
- 02/07/2011 11:00:01 1153
- 02/07/2011 11:00:01 1348
- 02/07/2011 11:00:01 1387
- 02/07/2011 11:00:02 1425
- 02/07/2011 11:00:03 1223
- ....
- 03/07/2011 12:00:01 1226
- 03/07/2011 12:00:01 1245
- 03/07/2011 12:00:01 1384
- 03/07/2011 12:00:02 1225
- 03/07/2011 12:00:03 1363
当有人选择一个月的日期时,我不知道如何平均饲料,它只显示当天的平均值/总和.
例如,在结果中,如果我选择日为02/07/2011.它会给我这样的东西：
- 02/07/2011 1234 (average value/or sum)
解决方法
早上7:40是00:00后的460分钟
晚上7:40是00:00后的1180分钟
midnigth是00:00后的1440分钟
和DATEPART(hh,SomeDate)* 60 DATEPART(mi,SomeDate)为您提供给定SomeDate 00:00后的分钟数
所以,你可以使用：
- SELECT
- AVG(Temperature) As Dayshift
- FROM Drye_data
- WHERE DATEPART(hh,TimeStamp)*60 + DATEPART(mi,TimeStamp) BETWEEN 460 AND 1180
- AND @SelectedDate = CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME)
而对于夜班：
- SELECT
- AVG(Temperature) As Nigthshift
- FROM Drye_data
- WHERE (
- (DATEPART(hh,TimeStamp) BETWEEN 0 AND 460)
- AND @SelectedDate = DATEADD(dd,1,CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME))
- )
- OR
- (
- (DATEPART(hh,TimeStamp) BETWEEN 1180 AND 1440) AND @SelectedDate = CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME)
- )
