Calculations across multiple tables

I have a time tracking app where (pseudo-code)

Client has_many Task has_many TimeRecord

TimeRecord
start_at, :datetime
end_at, :datetime

What I’m trying to do (and I have done using find_by_sql in the past) is
get
a sum of the timespan given by ended_at - begun_at for each client.

Is it naive to imagine that AR’s calculations will span relations to
provide
a grouped result?

The SQL I’m feeding to find_by_sql comes out something like this:

SELECT client_id, task_id, project_id, clients.client_name,
tasks.description,
time_records.start_at,
(SUM(TIME_TO_SEC(time_records.end_at) -
time_to_sec(time_records.start_at)) / 60) AS total_client_minutes,
(SUM(TIME_TO_SEC(time_records.end_at) -
time_to_sec(time_records.start_at)) / 3600) AS total_client_hours
FROM clients, tasks, time_records
WHERE tasks.client_id = clients.id AND time_records.task_id =
tasks.id
GROUP BY client_id, project_id, task_id WITH ROLLUP;

View this message in context:
http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5351226
Sent from the RubyOnRails Users forum at Nabble.com.

On Sun, 2006-07-16 at 11:24 -0700, s.ross wrote:

    (SUM(TIME_TO_SEC(time_records.end_at) - 

time_to_sec(time_records.start_at)) / 3600) AS total_client_hours
FROM clients, tasks, time_records
WHERE tasks.client_id = clients.id AND time_records.task_id = tasks.id
GROUP BY client_id, project_id, task_id WITH ROLLUP;


I’d be interested in others answers to your question since you may teach
me a new way but I recently finished a punch clock module in my
application and here’s how I handled it.

I have a column called pair_id and when ‘clocking in’ - a new pair_id is
incremented and saved with the entry. When ‘clocking out’, this same
pair_id is written to the record and I then use the pair_id to match the
‘clock out’ time to the ‘clock in’ time and do my math there. I also
have taken pains to prevent the user from creating a ‘clock in’ if their
last entry was a ‘clock in’ and likewise, a user is prevented from
creating a ‘clock out’ record if their last entry was a ‘clock out’

Craig

The wrinkle in this app is that any given task can have multiple time
records. E.g.:

Task id=1 / Did some coding on xyz, inc site
start 10:00 stop 10:15 when the phone rang

Task id=2 / Answered the phone from abc, inc
start 10:15 stop 10:21

Task id=1 Did some coding on xyz, inc site
start 10:22 stop 14:48

Because Task id 1 was interrupted, it has multiple time records, hence
the
odd requirement for calculations across relations.

View this message in context:
http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5351722
Sent from the RubyOnRails Users forum at Nabble.com.

On Sun, 2006-07-16 at 12:25 -0700, s.ross wrote:

start 10:22 stop 14:48

Because Task id 1 was interrupted, it has multiple time records, hence the
odd requirement for calculations across relations.


perhaps in your mind…in my mind, only one time clock could be open
anyway so when I started a new clock on task id=2, I would expect it to
put a stop on task id=1 (or any open task) so in my mind, that really
isn’t different but again, I am interested in how you solve it because
it may be better than my way.

Craig

The philisophical problem definition notwithstanding, I agree the
question is
whether AR has some slick capability for Rubyizing this kind of filtered
summarizing. As I said, it can be done using SQL aggregations, but then
we’re trying to to use SQL if we can abstract it, right?

FWIW, the rationale behind reusing task id’s is that there are a smaller
number of discrete tasks than there are periodic activities for them.
Tasks
have to be interruptable to be useful to me. Yes, they stop cold when
they
are interrupted, but they must resume and my device for resuming a task
is
simply to add a time record that describes the start and end of the new
activity.

Steve

Craig W. wrote:

perhaps in your mind…in my mind, only one time clock could be open
anyway so when I started a new clock on task id=2, I would expect it to
put a stop on task id=1 (or any open task) so in my mind, that really
isn’t different but again, I am interested in how you solve it because
it may be better than my way.

Craig


View this message in context:
http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5352534
Sent from the RubyOnRails Users forum at Nabble.com.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs