Re: Query design assistance - getting daily totals

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

am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes:
> 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

Why this broken data types? We have date and timestamp[tz].

> 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?

Yes, i would also write a similar function. And if you have proper
datatypes it would be simpler to calculate the previous date and you can
use a proper index on the date column.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

A. Kretschmer wrote:
> am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes:
>> year_id integer
>> month_id integer
>> working_day integer
>
> Why this broken data types? We have date and timestamp[tz].
>
>

It's a financial application which needs to work using a concept of
'financial periods' which may not necessarily correspond to calendar
months and it's much easier to manage in this way than it is to merge it
all together using a date field. Eg, 1st January may actually be the
15th 'working day' of the 9th 'financial period' - however looking at
just a date of jan-1 there is no way of knowing this and it's the
periods that matter more so than the actual date.

I've given the function method a try and it looks to work efficiently
enough.

P.

--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company

am Wed, dem 12.12.2007, um 15:39:48 +0900 mailte Paul Lambert folgendes:
> A. Kretschmer wrote:
> >am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes:
> >>year_id integer
> >>month_id integer
> >>working_day integer
> >
> >Why this broken data types? We have date and timestamp[tz].
> >
> >
>
> It's a financial application which needs to work using a concept of
> 'financial periods' which may not necessarily correspond to calendar
> months and it's much easier to manage in this way than it is to merge it

Ahh, thanks for the explanation.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On Dec 12, 2007 12:39 AM, Paul Lambert <paul(dot)lambert(at)reynolds(dot)com(dot)au> wrote:
> A. Kretschmer wrote:
> > am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes:
> >> year_id integer
> >> month_id integer
> >> working_day integer
> >
> > Why this broken data types? We have date and timestamp[tz].
> >
> >
>
> It's a financial application which needs to work using a concept of
> 'financial periods' which may not necessarily correspond to calendar
> months and it's much easier to manage in this way than it is to merge it
> all together using a date field. Eg, 1st January may actually be the
> 15th 'working day' of the 9th 'financial period' - however looking at
> just a date of jan-1 there is no way of knowing this and it's the
> periods that matter more so than the actual date.

I'm not sure that really justifies your method though. Not saying
"you're doing it wrong" so much as I'm not sure the way you're doing
it makes it any easier to keep track of certain periods. Any method
you would use to pick rows with the disjointed dates could be applied
to date and / or timestamp types as easily, and with some functional
indexes on the date / timestamp columns you could easily select
periods quickly as well.

Just saying.

On Dec 12, 2007 1:39 AM, Paul Lambert <paul(dot)lambert(at)reynolds(dot)com(dot)au> wrote:
> It's a financial application which needs to work using a concept of
> 'financial periods' which may not necessarily correspond to calendar
> months and it's much easier to manage in this way than it is to merge it
> all together using a date field. Eg, 1st January may actually be the
> 15th 'working day' of the 9th 'financial period' - however looking at
> just a date of jan-1 there is no way of knowing this and it's the
> periods that matter more so than the actual date.

I think what you need is a Calendar Table to "map" actual dates to
"buckets" e.g. 'financial periods', etc. See:

http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html