Message-ID: <475F3AAB.2050609@reynolds.com.au>
Date: Wed, 12 Dec 2007 10:34:35 +0900
From: Paul Lambert <paul.lambert@reynolds.com.au>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Query design assistance - getting daily totals
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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