# Calculations across multiple tables **URL:** \ **Category:** Rails\ **Created:** [July 16, 2006, 8:24pm UTC](https://www.ruby-forum.com/t/calculations-across-multiple-tables/98609 "2006-07-16T20:24:45Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [July 16, 2006, 8:24pm UTC](https://www.ruby-forum.com/t/calculations-across-multiple-tables/98609/1 "2006-07-16T20:24:45Z")
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](http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5351226) Sent from the RubyOnRails Users forum at [Nabble.com](http://Nabble.com). ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [July 16, 2006, 8:58pm UTC](https://www.ruby-forum.com/t/calculations-across-multiple-tables/98609/2 "2006-07-16T20:58:40Z")
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 ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [July 16, 2006, 9:26pm UTC](https://www.ruby-forum.com/t/calculations-across-multiple-tables/98609/3 "2006-07-16T21:26:45Z")
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](http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5351722) Sent from the RubyOnRails Users forum at [Nabble.com](http://Nabble.com). ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [July 16, 2006, 10:22pm UTC](https://www.ruby-forum.com/t/calculations-across-multiple-tables/98609/4 "2006-07-16T22:22:57Z")
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 ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [July 16, 2006, 10:58pm UTC](https://www.ruby-forum.com/t/calculations-across-multiple-tables/98609/5 "2006-07-16T22:58:04Z")
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](http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5352534) Sent from the RubyOnRails Users forum at [Nabble.com](http://Nabble.com).