Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnd
Starting Member
1 Post |
 Posted - 2001-12-17 : 17:09:09
|
| I have a table with a datetime field and an int field which contains the elapsed time a test required. I need a query to return the average of the elapsed times for 5 minute intervals throughout the day. I've already done queries using DatePart/Grouping to get the avg by Day or Hour but the 5 minute interval requirement has me stumped. Suggestions ?Thanks,JohnEFT Inc. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-17 : 17:53:39
|
| The DatePart() function will extract the minute values from a date value:SELECT DatePart(minute, dateCol) FROM myTableYou can then divide this by 5 (integer); this will round everything off to the nearest 5 minute interval, and GROUP BY it:SELECT DatePart(minute, dateCol)/5 FiveMinutes, Count(*) Total FROM myTable GROUP BY DatePart(minute, dateCol)/5Actually, that's not entirely what you want, because that would ignore the hours. This should do it:SELECT DatePart(hour, dateCol) Hour, DatePart(minute, dateCol)/5 FiveMinutes, Count(*) Total FROM myTable GROUP BY DatePart(hour, dateCol), DatePart(minute, dateCol)/5 |
 |
|
|
|
|
|
|
|
Query to return Avg data for 5 minute intervals