Avatar of edrz01
Flag for United States of America 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

Node1          32.5
Node2          22
Node3          34.75

Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon
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

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

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

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
Ephraim Wangoya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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


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

Thanks again.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.