Getting an average of a column per day

Rails
1

I’m trying to generate a report for the average number of calls a person
makes per-day. The following generates the correct list, but if a user
didn’t make any calls a certain day it doesn’t show up.

from_date = Time.now.at_beginning_of_month.to_s(:db)

to_date = Time.now.tomorrow.to_s(:db)

ave_calls = @current_user.calls.average(:num_of_calls,
:group => “DATE(calls.start_time_stamp)”,
:conditions => [“calls.created_at BETWEEN ? AND ?”,
from_date, to_date])

What is the best way of doing this? An example I have right now, I have
data from August 3rd til Today, no calls were made August 2nd or August
1st. The list comes back and starts at the 3rd.

2

Ryan K. wrote:

I’m trying to generate a report for the average number of calls a person
makes per-day. The following generates the correct list, but if a user
didn’t make any calls a certain day it doesn’t show up.

Why would a day with no calls appear? Its just reporting the DB data in
an aggregated format.

Merge the returned ave_calls with a list built out of an iteration of
from_date to to_date by day to fill the gaps.

3

Ar Chron wrote:

Ryan K. wrote:

I’m trying to generate a report for the average number of calls a person
makes per-day. The following generates the correct list, but if a user
didn’t make any calls a certain day it doesn’t show up.

Why would a day with no calls appear? Its just reporting the DB data in
an aggregated format.

Merge the returned ave_calls with a list built out of an iteration of
from_date to to_date by day to fill the gaps.

call_aves = (Time.now.at_beginning_of_month…Time.now.tomorrow).map{|dt|
[dt, ave_calls[dt.strftime “%Y-%m-%d”] || 0]}

At first, I just stuck call_aves into the value for Open Flash Charts,
and it broke it, kind’ve looked like it went into an infinite loop. So I
went into my debugger to see what was happening.

irb(#ReportsController:0x47b7524):008:0> call_aves[0]
=> [Sat Aug 01 00:00:00 -0400 2009, 0]

As I go up in the array, i notice it’s only incrementing by seconds, not
by days. For example:

irb(#ReportsController:0x47b7524):009:0> call_aves[30]
=> [Sat Aug 01 00:00:30 -0400 2009, 0]

How do I get this to go up by the day, not the second?

4

How do I get this to go up by the day, not the second?

Change your grouping function in the SQL, rather than

DATE(calls.start_time_stamp)

can you use just the day of the month portion? Perhaps

DAYOFMONTH(calls.start_time_stamp) (or just
DAY(calls.start_time_stamp))

as long as you’re selecting within a month, like start of month to
current date.

5

On Aug 13, 6:46 pm, Ryan K. [email protected]
wrote:

As I go up in the array, i notice it’s only incrementing by seconds, not
by days. For example:

irb(#ReportsController:0x47b7524):009:0> call_aves[30]
=> [Sat Aug 01 00:00:30 -0400 2009, 0]

How do I get this to go up by the day, not the second?

Well if you use dates rather than times that happens for free (since +
1 for a date means + 1 day), if not then you just need to put some
time values in an array, incrementing by a day at a time ( + 1.days or
using advance for example)

Fred