Re: Query design assistance - getting daily totals - Mailing list pgsql-sql

From Steve Midgley
Subject Re: Query design assistance - getting daily totals
Date
Msg-id 20071213211646.D6BF12E30DD@postgresql.org
Whole thread Raw
In response to Query design assistance - getting daily totals  (Paul Lambert <paul.lambert@reynolds.com.au>)
List 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: "Rodrigo De León" <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



pgsql-sql by date:

Previous
From: "Rolf A. de By"
Date:
Subject: Re: Trigger definition . . . puzzled
Next
From: Gavin Baumanis
Date:
Subject: SELECT MAX returns wrong value