asked on

Need query to calculate average data for a 5 day period

Need a query to calculate device average data for a rolling 5 day period. The results need to be for unique device names and the average of data for the past 5 days (including anything current).

Sample data:
Node1      31      2011-08-01
Node1      25      2011-08-01
Node1      43      2011-08-01
Node1      10      2011-08-03
Node1      52      2011-08-04
Node1      34      2011-08-04
Node2      17      2011-08-01
Node2      27      2011-08-03
Node3      43      2011-08-01
Node3      10      2011-08-03
Node3      52      2011-08-04
Node3      34      2011-08-04
-------

Results
Node1 32.5
Node2 22
Node3 34.75

Ideas?

Ephraim Wangoya

select Node, Avg(Nodedata)
from NodeTable
where NodeDate > dateadd(dd, -5, GETDATE())
group by Node

Ephraim Wangoya

change it to

select Node, Avg(Nodedata)
from NodeTable
where NodeDate >= dateadd(dd, -5, datediff(dd, 0, getdate()))
group by Node

Lee

select device, avg(cast(num as float)) from xtest
group by device

edrz01

ASKER

Ok, maybe more clarification is needed...

The query I am using actually joins from another table so not sure if that complicates things. The query I am using is

SELECT C.Caption, C.AvgLoad
from CPULoadByDays C
JOIN Nodes N
ON C.NodeID = N.NodeID
WHERE (N.Tower = 'DST' and avgload > 25)
AND C.DateTime BETWEEN DateAdd(dd, -5, GetDate()) and GetDate()
GROUP by Caption, AvgLoad

Produces:
Node1 64
Node1 74
Node2 26
Node2 27
Node2 29
Node3 29
Node4 31

Need it to be
Node1 21
Node2 24
Node3 29
Node4 31

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deighton

Try this for a rolling average

SELECT     n1.node,n1.logdate,  AVG(cast(n2.VALX as float)) AS FiveDayRolling from 
	(select node, logdate, sum(value) AS VALX FROM nodetable GROUP BY node,logdate) n1 join 
	(select node, logdate, sum(value) AS VALX FROM nodetable GROUP BY node,logdate) n2 
	ON n2.logdate<= n1.logdate and n2.logdate> dateadd(day,-5,n1.logdate) AND n1.node=n2.node

GROUP BY n1.node,n1.logdate
ORDER BY n1.node, n1.logdate

Open in new window

edrz01

ASKER

Thanks ewangoya, that worked like a champ. I did not explore the other suggestion as this one was clean and simple.

Thanks again.