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, [EMAIL PROTECTED] wrote:
Date: Wed, 12 Dec 2007 14:53:08 -0500
From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <[EMAIL PROTECTED]>
To: "Paul Lambert" <[EMAIL PROTECTED]>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query design assistance - getting daily totals
Message-ID: <[EMAIL PROTECTED]>

On Dec 12, 2007 1:39 AM, Paul Lambert <[EMAIL PROTECTED]> 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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

Reply via email to