How to get a sliding average value for periodic stored values.

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to get a sliding average value for periodic stored values.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-03 : 08:17:02

MiaWie writes "Hi experts,

you are nearly my last chance to solve a sql problem. I hope that someone can help me.

Keywords: average, time offset, data period

I want to get data from a database in Excel to calculate a mathematic model. The problem is, that the model requires averaged data. I have 26 variables, which are stored every 10 seconds. What I would like to get is a sliding average value with 5 minutes before and five minutes after a specific time.


+---------------------+---------+-------------
|     DateTime        | Value 1 | Avg(Value1) 
|---------------------+---------+-------------
|3/14/2003 2:55:00 PM |   856   |     850    
|3/14/2003 2:56:00 PM |   850   |     851 
|3/14/2003 2:57:00 PM |   852   |     851
|3/14/2003 2:58:00 PM |   600   |     850
|3/14/2003 2:59:00 PM |   860   |     849
|3/14/2003 3:00:00 PM |   854   |     850
|3/14/2003 3:01:00 PM |   850   |     850
|3/14/2003 3:02:00 PM |   849   |     849
|3/14/2003 3:03:00 PM |   865   |     850
|3/14/2003 3:04:00 PM |   851   |     850
|3/14/2003 3:05:00 PM |   849   |     850
|3/14/2003 3:06:00 PM |   855   |     851

What I have now is:

SELECT
CONVERT(VARCHAR(10), DateTime, 110) + ' ' + CONVERT(VARCHAR(2), DateTime, 108) + ':' + CONVERT(VARCHAR(2), FLOOR(DATEPART(mi, DateTime) / 5) * 5) AS 'Five Minute Period Starting',
TagName,
AVG(Value) AS 'Average'
FROM Runtime..AnalogHistory
WHERE
TagName IN ('Variable1', 'Varable2', 'Variable3') AND
DateTime >= '3/14/2003 2:55' AND
DateTime < '3/17/2003 11:00' AND
wwRetrievalMode = 'DELTA'
GROUP BY
CONVERT(VARCHAR(10), DateTime, 110) + ' ' + CONVERT(VARCHAR(2), DateTime, 108) + ':' + CONVERT(VARCHAR(2), FLOOR(DATEPART(mi, DateTime) / 5) * 5),
TagName

With this code I can get the average from the values in a five minute data period. This code doesn’t calculate a sliding average. But I need a sliding average to set a time offset or to get data period from, for example, an average value every two minutes.

I’m not sure if I’m describing my problem clearly.

Thanks a lot for your help in advance.

Best regards

Melanie"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-03 : 08:38:18

Try this:

Declare @StartTime datetime,
@EndTime datetime,
@interval int

Set @StartTime = '3/14/2003 2:55'
Set @EndTime = '3/17/2003 11:00'
Set @interval = 2

SELECT
dateadd(mi,floor(datediff(mi,@startTime,DateTime)/@interval)*@interval,@StartTime) AS 'Five Minute Period Starting',
TagName,
AVG(Value) AS 'Average'
FROM Runtime..AnalogHistory
WHERE
TagName IN ('Variable1', 'Varable2', 'Variable3') AND
DateTime >= @StartTime AND
DateTime < @EndTime AND
wwRetrievalMode = 'DELTA'
GROUP BY
dateadd(mi,floor(datediff(mi,@startTime,DateTime)/@interval)*@interval,@StartTime),
TagName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources