pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery design assistance - getting daily totals
7+ messages / 5 participants
[nested] [flat]
* Query design assistance - getting daily totals
@ 2007-12-12 01:34 Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 06:31 ` Re: Query design assistance - getting daily totals A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 7+ messages in thread
From: Paul Lambert @ 2007-12-12 01:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query design assistance - getting daily totals
2007-12-12 01:34 Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
@ 2007-12-12 06:31 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-12 06:39 ` Re: Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2007-12-12 06:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query design assistance - getting daily totals
2007-12-12 01:34 Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 06:31 ` Re: Query design assistance - getting daily totals A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-12-12 06:39 ` Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 07:29 ` Re: Query design assistance - getting daily totals A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-12 15:48 ` Re: Query design assistance - getting daily totals Scott Marlowe <scott.marlowe@gmail.com>
2007-12-12 19:53 ` Re: Query design assistance - getting daily totals Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Paul Lambert @ 2007-12-12 06:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query design assistance - getting daily totals
2007-12-12 01:34 Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 06:31 ` Re: Query design assistance - getting daily totals A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-12 06:39 ` Re: Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
@ 2007-12-12 07:29 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 0 replies; 7+ messages in thread
From: A. Kretschmer @ 2007-12-12 07:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query design assistance - getting daily totals
2007-12-12 01:34 Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 06:31 ` Re: Query design assistance - getting daily totals A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-12 06:39 ` Re: Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
@ 2007-12-12 15:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2007-12-12 15:48 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@reynolds.com.au>; +Cc: pgsql-sql
On Dec 12, 2007 12:39 AM, Paul Lambert <paul.lambert@reynolds.com.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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query design assistance - getting daily totals
2007-12-12 01:34 Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 06:31 ` Re: Query design assistance - getting daily totals A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-12 06:39 ` Re: Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
@ 2007-12-12 19:53 ` Rodrigo De León <rdeleonp@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Rodrigo De León @ 2007-12-12 19:53 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@reynolds.com.au>; +Cc: pgsql-sql
On Dec 12, 2007 1:39 AM, Paul Lambert <paul.lambert@reynolds.com.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query design assistance - getting daily totals
@ 2007-12-13 21:11 Steve Midgley <public@misuse.org>
0 siblings, 0 replies; 7+ messages in thread
From: Steve Midgley @ 2007-12-13 21:11 UTC (permalink / raw)
To: pgsql-sql
Hi,
Rodrigo is exactly right in my opinion. To provide a little more info
on this calendar or day dimension idea..
You can create, for example, a time table dimension which stores every
day of every year as a unique record (for as far into the future as you
need). You can then associate various attributes to each day, depending
on your business needs like so:
id|datetime|is_business_day|is_weekday|is_fed_holiday
Of course it's not normalized but that's the point. You then just store
the id in various places and it's easy to join back to this table and
figure out if a particular day has an attribute you're interested in
(or you can find the id's for all the days which have a particular
attribute for a given date range - to go the other direction, for
example).
You can get more on this type of thinking from the most excellent
resource by Ralph Kimball "The Data Warehouse Toolkit: The Complete
Guide to Dimensional Modeling (Second Edition)" - this book did more to
open my eyes to alternative to traditional "normalized" modeling than
anything else. It also made me feel less guilty about building certain
non-normal structures. :)
I hope that's helpful..
Steve
At 12:21 PM 12/13/2007, pgsql-sql-owner@postgresql.org wrote:
>Date: Wed, 12 Dec 2007 14:53:08 -0500
>From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <rdeleonp@gmail.com>
>To: "Paul Lambert" <paul.lambert@reynolds.com.au>
>Cc: pgsql-sql@postgresql.org
>Subject: Re: Query design assistance - getting daily totals
>Message-ID:
><a55915760712121153x5c9a10a1s89c737a44e4eb149@mail.gmail.com>
>
>On Dec 12, 2007 1:39 AM, Paul Lambert <paul.lambert@reynolds.com.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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-12-13 21:11 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-12 01:34 Query design assistance - getting daily totals Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 06:31 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-12 06:39 ` Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 07:29 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-12 15:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-12 19:53 ` Rodrigo De León <rdeleonp@gmail.com>
2007-12-13 21:11 Re: Query design assistance - getting daily totals Steve Midgley <public@misuse.org>
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