views:

74

answers:

0

I have a rake file that does a series of calculations on a database. Basically it adds usage on equipment regularly. At the end of the day it needs to add that days total to a monthly total table and update that same table. i use the following simple concepts:

To get data from the database is pretty simple:

  @usages = Usage.find(:all)
 time = Time.new
  for usage in @usages
  sql = ActiveRecord::Base.connection

To insert it into the database:

sql.execute "INSERT (or UPDATE) into usages ## add values and options as per MySQL

But how do I:

take a column from a database, add all of its values together that have a common value in another column, (i.e. if column x == value y) and then insert it into another column in another table, say dailyusages?

I have tried these options:

task (:monthly => :environment) do

@dailyusages = Dailyusage.find(:all)
 for dailyusage in @dailyusages
   sql = ActiveRecord::Base.connection

time = Time.new
  device = monthlyusages.device
  month = time.month

if device == dailyusages.device ##&& month == dailyusages.month

total = (dailyusage.total.sum.to_i)

  @monthlyusages = Monthlyusage.find(:all)
   for monthlyusage in @monthlyusages
   sql = ActiveRecord::Base.connection

  old_total = monthlyusage.total.to_i
  new_total = (old_total + total)

 sql.execute "UPDATE monthlyusages ( year, month, total, device ) values('#{time.year}', '#{time.month}', '#{total}', '#{dailyusage.device}' )"

 end

I obviously have uncommented options and tried all sorts of things. Any help would really save me a load of trouble.

Thanks in advance.

(** BTW - I am new to rails, so go easy on me **)