Possible Duplicate:
How to average/sum data in a day in SQL Server 2005
I have a set of data which is called Dryer_Data. This data is recorded every minute. I need to average it Dayshift: from 7:40AM to 7:40PM everyday, and Nightshift from 7:40PM to 7:40AM the next day
timestamp Temperature
02/07/2011 12:01:00 AM 1246
02/07/2011 12:02:00 AM 1234
02/07/2011 12:03:00 AM 1387
02/07/2011 12:04:00 AM 1425
02/07/2011 12:05:00 AM 1263
...
02/07/2011 11:02:00 PM 1153
02/07/2011 11:03:00 PM 1348
02/07/2011 11:04:00 PM 1387
02/07/2011 11:05:00 PM 1425
02/07/2011 11:06:00 PM 1223
....
03/07/2011 12:01:00 AM 1226
03/07/2011 12:02:00 AM 1245
03/07/2011 12:03:00 AM 1384
03/07/2011 12:04:00 AM 1225
03/07/2011 12:05:00 AM 1363
I did do something like
For NIGHTShift:
Select CONVERT(char(10), timestamp, 121), average(temperature)
From Drye_data
Group by CONVERT(char(10), timestamp, 121)
Having CONVERT(char(10), timestamp, 121) BETWEEN DATEADD(minute, 40, DATEADD(hour, 19, @selectdate)) AND DATEADD(minute, 40, DATEADD(hour, 31, @selectdate)))
I believe it only gives me data that from 12AM to 7:40AM.
For DAYShift I did:
Select CONVERT(char(10), timestamp, 121), average(temperature)
From Drye_data
Group by CONVERT(char(10), timestamp, 121)
Having CONVERT(char(10), timestamp, 121) BETWEEN DATEADD(minute, 40, DATEADD(hour, 7, @selectdate)) AND DATEADD(minute, 40, DATEADD(hour, 19, @selectdate)))
Of course, it doesn't give me any value.
Or instead of using CONVERT, I used DATEADD(hour, DATEDIFF(hour, 0, timestamp), 0), it gives the answer of every hour (multiple answer for every hour). But I only need 1 result.
Or when I used DATEADD(day, DATEDIFF(day, 0, timestamp), 0), it gives me the same answer with CONVERT. Please help.... THANK YOU VERY MUCH.
