<rishib4u_at_gmail.com> wrote in message
news:1109489589.988636.48240_at_g14g2000cwa.googlegroups.com...
> hi everyone,
> i have a query that needs to calculate the AVG of records taken at 15
> minutes interval 24/7 grouped by day for a date range. As per the
> requirements the AVG needs to consider the following guidelines -
> say the the time for which the data has to be avg'd up is 7:00 am to
> 8:00 am then the AVG would be for all data recorded at (7:15, 7:30,
> 7:45 & 8:00) excluding the first record at 7:00 am i.e. > 7:00 AM AND
> <= 8:00 AM.
> NOW the problem is that when i GROUP BY date the AVG function
> calculates the average of all records for that day including the record
> at 0:00 HRS and excluding the record at 24:00 HRS whence it should be
> the exact opposite as per the guidelines.
> Can anyone please suggest a solution?
> thankyou,
> rishi.
You're going to have to break down the date/time component into something
you can group by, i.e extract the hour and minutes as calculated columns and
GROUP BY hour. If you only want the rage HH:01 to HH+1:00 you're going to
have to get fancy and use the modulus function, or some other method to
derive your hourly grouping factor.
Received on Sun Feb 27 2005 - 13:47:12 CST
