Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Rolling average query

Posted on 2002-07-09
8
Medium Priority
?
552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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