Rails Getting a running balance with ordered list - summation virtual attribute

Question

I'm trying to get a current balance per transaction, a simple summation (Credit's up to record - Debits up to record) on an ordered list.

The challenge is that transactions are often entered out of order, and the transaction logs are sorted by a "Dated" field (showing the date it should have been entered) and by "credits" first (so that it shows payments before bills on the same day).

The association is sorted:

has_many :rent_logs, :order => [:dated, "credit desc"]

Data is presented simular to the following

[ID] Dated  Label   Credit  Debit   Balance [Needs excel summation look]
[20] 1/1/13 payment 600.0          -30 *    [Should be: 600 ]
[1 ] 1/1/13 Rent Due        630.0  -30      [Ok here  : -30 ]
[2 ] 2/1/13 Rent Due        630.0  -660     [Ok here  : -660]
[28] 2/6/13 Late Fee        50.0   -710     [Ok here  : -710]
[7 ] 3/1/13 payment 1200.0         -140*    [Should be: 490 ]
[3 ] 3/1/13 Rent Due        630.0  -140     [Ok here  : -140]

* Indicates massive fail on running balance

I got this by running the method below in the lease model.

def balance_to_date(date)
 ...
  rent_logs.where("dated <= ?", date).sum(:credit) - rent_logs.where("dated <= ?" ,date).sum(:debit)
  #problem with above is that it calculates day by day, rather than record by record.
end

The problem is that I don't want it to get a difference of all the prior through out the date of interest. I want it to get the difference of all the prior through the *current record.

There are no other obvious propertys to do conditions or filters on that I can think of. The best solution that I can think of is an ugly one that probably should get me fired...:

def balance_to_transaction(id)
  balance = rent_logs.where("dated <= ?", date-1.day).sum(:credit) - rent_logs.where("dated <= ?" ,date-1.day).sum(:debit)
  rent_logs.where("dated = ?", date).each do |transaction|
    balance += transaction.credit
    balance -= transaction.debit
    if (id == transaction.id) 
      break
    end
  end
  balance
end

This cannot be the right way to do this?

I'm using Rails 3.2.12, Ruby 1.9.3

Thanks Phil

score 1 · Accepted Answer · answered Mar 05 '13 at 09:50

1

The problem is that you use the date as condition, which makes no difference between the records in the same day. To solve this use ID to differenciate between the records.

def balance_to_item(date, id)
 ...
  rent_logs.where(["dated < ? or (dated = ? and ID <= ?)", date, date, id]).sum(:credit) - 
  rent_logs.where(["dated < ? or (dated = ? and ID <= ?)", date, date, id]).sum(:debit)
end

You need to specify the transaction item to calculate the balance up to.

answered Mar 05 '13 at 09:50

Matzi

13,770
4
33
50

Will this work if the id's are not part of the ordering? For example, id 5 might come right after id 6, rather than natural counting 4,5,6 it could look like 20,**6**,5,7,8. but I use :credit to sort by, so that might do it... – nevieandphil Mar 05 '13 at 11:37
If id is not part of the ordering then it will result is somewhat mixed up balance orders. If you use credit to order, then the null values vill also mix up things, that is why I recommend you to use ID instead, even in the ordering. It only changes the order inside a given day, so won't be mixed up. – Matzi Mar 05 '13 at 12:04
Yeah, once I simplify the order by for the association to [:dated,:id], this works great, and is much simpler and cleaner than any other implementation that I was comping up with. Thanks Matzi! – nevieandphil Mar 05 '13 at 12:59

Rails Getting a running balance with ordered list - summation virtual attribute

1 Answers1