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 |
|
nooch
Starting Member
22 Posts |
 Posted - 2007-11-15 : 08:39:03
|
| I have a table with two fields in - the date/time and a total, below is an example: a_datetime | a_total-----------------------------2007-11-15 09:34:05 | 372007-11-15 09:24:05 | 242007-11-15 09:15:05 | 92007-11-15 09:11:05 | 64This table currently hold 14k+ records, and the earliest date is around august 2007.I want to use the information to power a graph. The graph i am creating has 60 vertical bars in it representing 60 time intervals between the earliest and latest record in the table. So... i want to pull out of the table 60 times/time-periods, and 60 Averages of (a_total)an example of the output might be something like:a_datetime | AVE(a_total)------------------------------------------------------------2007-11-03 07:57:02 - 2007-11-15 09:11:05 | 152007-10-22 06:21:19 - 2007-11-03 07:57:02 | 34etc...I was thinking maybe some kind of group by on datetime/60 but i am completely confused. Can anyone point me in the right direction?Thanks in advance guysDanny |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-15 : 09:54:17
|
| First you need to get to those 60 points of observation by shaping out your datetime column, which might also has a high distinct count. Things you may do includes: strip out the time portion, break them down to ranges, use where clause to exclude the ones you don't want to include.Then get the avg by group by. |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-15 : 12:43:26
|
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspxthe link has a lot of useful info on date time use.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
GROUP BY?