?
Solved

Rolling average query

Posted on 2002-07-09
8
Medium Priority
?
556 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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