Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: Rolling One-Hour Average

    I have a group of data with station, date, time, and value and I need to average the value within a rolling one hour average using a query and not VBA. Note: I keep my date and time in seperate fields even though their fields are date/time fields.

    Here is a sample set:

    SW1 2/3/2014 9:10 90
    SW1 2/3/2014 10:30 87
    SW1 2/3/2014 11:00 89

    I should get two records/averages from this data:

    SW1 2/3/2014 88.5
    SW1 2/3/2104 88

  2. #2
    Join Date
    Nov 2012
    Posts
    32
    Hi Geopup,

    If you your DB is designed in a way that there can be only one record per hour, then it can be easily solved by joining the table/query on another instance of the same table/query.

    First, you create a simple select query from the table you have and add an isolated hour myHour filed Hour([your_time]) and a field you use for joining, Hour([your_time]) - 1, I'll call that field myHourNext.

    It will look like this:
    Code:
    SELECT
       tbl_Values.myID,
       tbl_Values.myDate,
       Hour([myTime]) AS myHour,
       Hour([myTime])-1 AS myHourNext,
       tbl_Values.myValue
    FROM tbl_Values
    and return something like this
    SW1 2/3/2014 9:10 9 8 90
    SW1 2/3/2014 10:30 10 9 87
    SW1 2/3/2014 11:00 11 10 89


    Then you create another query that will join the previous query on itself by date on date and myHour on myHourNext. Then you create an average field by adding values from both queries and dividing them by 2 like this:
    Code:
    SELECT
       Calc1.myDate,
       Calc1.myHour,
       ([calc1].[myValue]+[calc2].[myValue])/2 AS myAvg
    FROM qry_Calc AS Calc1
       INNER JOIN qry_Calc AS Calc2 ON (Calc1.myDate = Calc2.myDate)
       AND (Calc1.myHour = Calc2.myHourNext);
    Which will return:
    SW1 2/3/2014 9 88.5
    SW1 2/3/2014 10 88

    This will only work if you are certain that there can be only one record per hour, otherwise different logic involving aggregations would be needed.

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    Unfortunately, the times can be any time not just once an hour. Here is another example of some of my data that I have to average all at same site and same day: 9:30 84, 9:45 86, 9:50 87 and 10:47 80. That would make 2 one-hour averages. The first three times would be average 1 of 85.7 and the last two times would be average 2 of 83.5.
    Last edited by geopup1; 04-09-14 at 12:31.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you'd need to rethink your storage date and time should be in the same column, not separated. Its going to make the query needlessly complex. you may get away with it if there are no values which cross midnight.

    what you could try is something like
    Code:
    SELECT A.SampleTime
    , count(B.Reading) as noValues
    , format(avg(B.Reading),"0.0000") as MovingAvg
    FROM mytable as A, mytable AS B
    where B.SampleTime Between dateadd("h",-1, A.Sampletime)  AND a.sampletime
    group by a.sampletime
    ;
    you'd need to change the name of the table and columns to match yours

    you shoudl get somethign similar to:-
    Code:
    SampleTime	noValues	MovingAvg
    03/03/2014 09:10:00	1	90.0000
    03/03/2014 09:30:00	2	87.0000
    03/03/2014 09:45:00	3	86.6667
    03/03/2014 09:50:00	4	86.7500
    03/03/2014 10:30:00	4	86.0000
    03/03/2014 10:47:00	3	84.6667
    03/03/2014 11:00:00	3	85.3333
    the reason why Microsoft and other db vendors provide date time functions is to do things like this
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    of course you could be a smarty pants and specify the interval as either a parameter or modify the query. that way round when the boss wnats say half hourly intervals, of daily, or whatever they can calculate the average as and when they require at what ever intervale they require

    ..you'd probably change the dateadd to specify the interavl to be "n" (minutes) and the value to be - whatever (1 hour is -60...)
    Note Between MUST have the lowest value first
    you can use either dateadd or its synonym datesub, although datesub doesn't require the minus sign as by definition you are subtracting
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •