10 minute averages - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

10 minute averages

Author

Topic

Grayco
Starting Member

22 Posts

Posted - 2010-04-07 : 18:47:15

Greetings,
I am working with a large amount of quantitative data. All my records are associated to a timestamp. The records and timestamps are collected every two seconds.

I'm using Microsoft SQL sever 7 and the timestamp is the datetime format.

I can parse each character in the timestamp using the Left and Right functions. The parse will look something like this.

Left(Right(timestamp, 4),1) operand

What I want to do is select an average from each 10 minutes of records.

I'm thinking there must be a way to average all the records where the single minute position of the timestamp is inclusivly between 0 and 9.

It might look something like this;
select average(Left(Right(timestamp, 4),1),between 0 and 9)

I want to report the average value for the previous 10 minutes and associate the value to the 10 minute timestamp.

I'm not sure how to start. Do I have to create some type of variable that is equal to the average values and then group by that variable?

I would appreciate hearing your thoughts on this
Thanks
Glen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-07 : 22:14:08

[code]
select dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0), avg ( value )
from yourtable
group by dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0)
[/code]

change the table name / column name in italic to the actual name

KH
[spoiler]Time is always against us[/spoiler]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-04-07 : 22:15:25

Just set the timestamp to the beginning of the 10 minute time period, and group by that.


Select
	a.MyDate,
	Start_of_10_Min =
	dateadd(mi,(datepart(mi,a.MyDate)/10)*10,dateadd(hh,datediff(hh,0,a.Mydate),0))
from
	( -- Test Data
	select MyDate = getdate()
	) a

Results:


MyDate                  Start_of_10_Min
----------------------- -----------------------
2010-04-07 22:10:37.447 2010-04-07 22:10:00.000

(1 row(s) affected)

More info on how to do this here:
Start of Time Period Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

Edit:
The method that khtan posted is shorter code, but will overflow on datetime values >= year 5984. The version I posted works with all possible date time values. Probably not important for the vast majority of applications.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-07 : 23:13:13

Good Point there

KH
[spoiler]Time is always against us[/spoiler]

Grayco
Starting Member

22 Posts

Posted - 2010-04-08 : 13:59:39

Is there a way I can simulate microsoft SQL server 7 at home?

quote:
Originally posted by Michael Valentine Jones

Just set the timestamp to the beginning of the 10 minute time period, and group by that.

Select
a.MyDate,
Start_of_10_Min =
dateadd(mi,(datepart(mi,a.MyDate)/10)*10,dateadd(hh,datediff(hh,0,a.Mydate),0))
from
( -- Test Data
select MyDate = getdate()
) a

Results:

MyDate Start_of_10_Min
----------------------- -----------------------
2010-04-07 22:10:37.447 2010-04-07 22:10:00.000

(1 row(s) affected)

More info on how to do this here:
Start of Time Period Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

Edit:
The method that khtan posted is shorter code, but will overflow on datetime values >= year 5984. The version I posted works with all possible date time values. Probably not important for the vast majority of applications.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-04-08 : 14:30:20

quote:
Originally posted by Grayco

Is there a way I can simulate microsoft SQL server 7 at home?
...

What do you mean by "simulate microsoft SQL server 7 at home"?

What does that have to do with 10 minute averages?

CODO ERGO SUM

Grayco
Starting Member

22 Posts

Posted - 2010-04-08 : 15:18:12

I won't be back to work until next Sunday. I would like to build a small database at home that will simulate the columns I work with at work.

As far as what that has to do with 10 minute averages, I would like to try out the answers that are graciously being supplied.

Your point is well taken though. I will start a new thread that is specific to the creation of a test database.

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by Grayco

Is there a way I can simulate microsoft SQL server 7 at home?
...

What do you mean by "simulate microsoft SQL server 7 at home"?

What does that have to do with 10 minute averages?

CODO ERGO SUM

Grayco
Starting Member

22 Posts

Posted - 2010-04-10 : 12:40:37

Good Morning khtan,
I'm accessing a test table in MS Access through Excel in MS Query.

I ran the following;

select dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0), avg ('data' )
FROM `C:\Documents and Settings\Glen\My Documents\SQL Test DB`.Sheet1 Sheet1
group by dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0)

I got the following error;

"Too few parameters. Expected 1."

I've been beating the internet up and have not been able to see which parameter I'm missing. Do you see it?

Thanks
Glen

quote:
Originally posted by khtan

select dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0), avg ( value )
from yourtable
group by dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0)

change the table name / column name in italic to the actual name
KH
[spoiler]Time is always against us[/spoiler]

Grayco
Starting Member

22 Posts

Posted - 2010-04-10 : 12:48:31

Good Morning Michael,

My test table is a MS Access table. I'm accessing it in Excel through MS Query. I have two columns, timestamp and data.

I'm a confused by this code. Which elements of your code equal timestamp and which represent data?

Thanks
Glen

quote:
Originally posted by Michael Valentine Jones

Just set the timestamp to the beginning of the 10 minute time period, and group by that.

Select
a.MyDate,
Start_of_10_Min =
dateadd(mi,(datepart(mi,a.MyDate)/10)*10,dateadd(hh,datediff(hh,0,a.Mydate),0))
from
( -- Test Data
select MyDate = getdate()
) a

Results:

MyDate Start_of_10_Min
----------------------- -----------------------
2010-04-07 22:10:37.447 2010-04-07 22:10:00.000

(1 row(s) affected)

More info on how to do this here:
Start of Time Period Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

Edit:
The method that khtan posted is shorter code, but will overflow on datetime values >= year 5984. The version I posted works with all possible date time values. Probably not important for the vast majority of applications.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-11 : 02:59:06

quote:
Originally posted by Grayco

Good Morning khtan,
I'm accessing a test table in MS Access through Excel in MS Query.

I ran the following;

select dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0), avg ('data' )
FROM `C:\Documents and Settings\Glen\My Documents\SQL Test DB`.Sheet1 Sheet1
group by dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0)

I got the following error;

"Too few parameters. Expected 1."

I've been beating the internet up and have not been able to see which parameter I'm missing. Do you see it?

Thanks
Glen

quote:
Originally posted by khtan

select dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0), avg ( value )
from yourtable
group by dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0)

change the table name / column name in italic to the actual name
KH
[spoiler]Time is always against us[/spoiler]

are you asking about a way to do it in access?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grayco
Starting Member

22 Posts

Posted - 2010-04-11 : 13:16:50

No. I'm using MS Query to access a table in Access. I'm trying to figure out how to resolve the error message;
"Too few parameters. Expected 1."

Thanks again
Glen

[/quote]
[/quote]
are you asking about a way to do it in access?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources