Re: Query design assistance - getting daily totals

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(at)postgresql(dot)org wrote:
>Date: Wed, 12 Dec 2007 14:53:08 -0500
>From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <rdeleonp(at)gmail(dot)com>
>To: "Paul Lambert" <paul(dot)lambert(at)reynolds(dot)com(dot)au>
>Cc: pgsql-sql(at)postgresql(dot)org
>Subject: Re: Query design assistance - getting daily totals
>Message-ID:
><a55915760712121153x5c9a10a1s89c737a44e4eb149(at)mail(dot)gmail(dot)com>
>
>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