Solved

Rolling average query

Posted on 2002-07-09
8
542 Views
Last Modified: 2010-08-05
I have a database with the first column which is datestamp (dd/mm/yy hh/nn/ss) and second column is readings at 10 minute intervals.

I want to set up a query which does a rolling average of readings over the previous 12 hours for the previous 7 days (so I'll need 7 1/2 days of data for 7 days of averages).

I'm sure it's not impossible, but I have absolutely no idea of how to go about it.

Many thanks
0
Comment
Question by:bigc
8 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 7141283
Here goes the first attempt

SELECT Int((Now()-[DateStamp])*24/12) AS Expr2, Avg(tblReadings.Reading) AS AvgOfReading
FROM tblReadings
WHERE (((Int([DateStamp]))>=Date()-7 And (Int([DateStamp]))<Now()) AND ((Int((Now()-[DateStamp])*24/12)/2)=Int(Int((Now()-[DateStamp])*24/12)/2)))
GROUP BY Int((Now()-[DateStamp])*24/12)
WITH OWNERACCESS OPTION;

My Test Data was.
DateStamp     Reading
09/07/2002 18:00:00     10
09/07/2002 17:50:00     11
08/07/2002 18:00:00     12
02/07/2002 18:00:00     10
10/07/2002 08:00:00     1000
09/07/2002 08:00:00     100
09/07/2002 06:00:00     10000

Cheers, Andrew
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7141297
Nice problem ;-)
It also raises some questions:
1) Are the readings without gaps?
   If there are, what to do ?
2) Are the readings countvalues to be averaged or
   are the readings the value of a counter, so they need to be subtracted to get the difference for the actual calculation.

Nic;o)
0
 
LVL 18

Expert Comment

by:deighton
ID: 7142843
I defined a table called tblReadings with a date field called datTime and a numeric field called reading.

The best way seems to be to calculate the total for the last 12 hours and the count of readings in the last 12 hours using subqueries renaming the table

SELECT tblReadings.datTime, tblReadings.Reading, (select sum(tblReadings2.reading) FROM tblReadings AS tblReadings2 where tblreadings2.datTime >=  tblReadings.datTime - .5 and  tblreadings2.datTime <=  tblReadings.datTime) AS Summed, (select count(tblReadings3.reading) FROM tblReadings AS tblReadings3 where tblreadings3.datTime >=  tblReadings.datTime - .5 and  tblreadings3.datTime <=  tblReadings.datTime) AS Counted
FROM tblReadings


you can then query the query to get values over 7 days

SELECT summed/counted AS RollingAverage, *
FROM [SELECT tblReadings.datTime As ReadTime, tblReadings.Reading, (select sum(tblReadings2.reading) FROM tblReadings AS tblReadings2 where tblreadings2.datTime >=  tblReadings.datTime - .5 and  tblreadings2.datTime <=  tblReadings.datTime) AS Summed, (select count(tblReadings3.reading) FROM tblReadings AS tblReadings3 where tblreadings3.datTime >=  tblReadings.datTime - .5 and  tblreadings3.datTime <=  tblReadings.datTime) AS Counted
FROM tblReadings]. AS [%$##@_Alias]
WHERE ReadTime >=now - 7 and readtime <= now;






0
 

Author Comment

by:bigc
ID: 7142873
nic;o)

I've never been complimented on the quality of my questions, but I'll take it as a compliment anyway - after all, a girl has to take them where she can get them ;-)

In answer to your questions, there shouldn't be any gaps in the data, and if there is, we'll just average over what there is.  The readings themselves are voltages (all between 11 & 13), which are later converted into lengths (I'm monitoring movement).  The overall movement is fairly slow, but the instrument reading them is showing more movement than we know is occurring - the averaging smooths this out.  Hope that isn't too much detail - no one ever said the world of civil engineering was exciting.

I'll have a look at the suggestions so far and get back to you.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 18

Expert Comment

by:deighton
ID: 7142901
..its always best to give your table name and field names, in this case it is only 3 names

- then the sql we make will be more likely to work directly.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7142913
bigc,

Some Q's are just more fun as others and this is just one that's putting the brain to work ;-)
As the number of measurements in the 12 hrs can vary, we'll need the SUM / COUNT calculation deighton used in his query.

Straight forward this can be done using DSUM and DCOUNT, it will however be slow...
You'll get a query like:
SELECT Datestamp, reading, DSUM("reading","tblReadings","datestamp BETWEEN " & [DateStamp] & " AND "& [DateStamp]-.5)/DCOUNT("reading","tblReadings","datestamp BETWEEN " & [DateStamp] & " AND "& [DateStamp]-.5) AS RollingAverage  
FROM tblReadings
WHERE Datestamp > NOW()-7.5;

Personally I would probably use a recordset processing function to speedup things.
BTW I think it's a bad idea to use an average to smoothen the error measurements out.
I would probably go for using the 95 percentile of the measurements, to drop the extreme measurements both the high and the low ones...

Success !

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7260159

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7296329
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now