Re: Using generate_series to create a unique ID in a query?

On Nov 12, 2007, at 5:11 PM, Sarah Dougherty wrote:
> For some context, I am trying to create a report that provides a
> list of client charges and payments and a "running balance" after
> each transaction. Because we often have multiple charges and/or
> payments on the same day, we can't use the transaction date to
> calculate this balance. Instead, I want to calculate our running
> balance by assigning a transaction ID to each transaction a d then
> having the query sum up transaction amounts for all transactions
> with an equal or lower ID.

I think you'd be much better off writing a function that does this
for you... it'd have to accept and amount and then keep a running
total. Wouldn't be a bad idea to have a boolean you can pass in that
will reset the total, too... that would allow using it in a grouping
scenario. Erm, I guess you'd actually have to accept something like a
hash; pass in a hash of all the grouping fields and whenever that
changes you reset the total.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828