Solved

Rolling average query

Posted on 2002-07-09
8
541 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

15 Experts available now in Live!

Get 1:1 Help Now