Re: Query design assistance - getting daily totals

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