From: Paul Lambert <paul.lambert@reynolds.com.au>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Query design assistance - getting daily totals
Date: Wed, 12 Dec 2007 10:34:35 +0900
Message-ID: <475F3AAB.2050609@reynolds.com.au> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: paul.lambert@reynolds.com.au
Subject: Re: Query design assistance - getting daily totals
In-Reply-To: <475F3AAB.2050609@reynolds.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox