I have a table of account balances as at the end of a working day and
want to from that, calculate daily total figures.
Eg, let's say I have a table structure of:
year_id integer
month_id integer
working_day integer
account integer
account_balance numeric(19,4)
Example data might be something like
2007,12,1,1,100.00
2007,12,2,1,200.00
2007,12,3,1,250.00
2007,12,4,1,500.00
2007,12,5,1,575.00
I want to construct a query that will give me the daily balances from
this information, so I would be presented with something like:
2007,12,1,1,100.00
2007,12,2,1,100.00
2007,12,3,1,50.00
2007,12,4,1,250.00
2007,12,5,1,75.00
I figure there's a couple of ways I could do it...
Firstly, build a complicated nested select where the lower level gets
the main data, then the outer select joins it on itself where the
working_day is equal to the working_day-1 from the nested query and then
wrap that in another select that calculates the difference in the
account_balance column from both.
The second option I think would be to create a function whereby I pass
it the primary key fields (year_id,month_id,working_day,account) and
have it do two selects and work out the difference.
I suspect the second option would be more efficient than the first, and
probably easier to implement since it would be easier to handle
cross-month boundaries, i.e. day 1's daily total will be the amount on
that day minus the amount of the final day in the previous month - but
does anyone have any alternate suggestions that would be better still?
Cheers,
Paul.
--
Paul Lambert
Database Administrator
AutoLedgers