我试图在一天内平均sql Server 2005中的数据.如果我使用简单查询,这是我的数据库看起来像这样
SELECT timestamp,Feed FROM ROASTER_Feed ORDER timestamp
数据：
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)
)
