CREATE TABLE calendar_dim(
month_abbr VARCHAR2 )
The calendar_dim table is then populated with the calendar data that you need like:
1, 2006, 1, 1, 'January', 'JAN'
2, 2006, 1, 2, 'February', 'FEB'
Each table that contains dated information has a calendar_id column like:
CREATE TABLE foo_bar(
calendar_id NUMBER NOT NULL,
bar NUMBER )
The calendar dimension can then be used to control the time frame that queries are run for. As an example the following query sums the value bar over the first quarter of the year 2006.
SELECT fb.foo, SUM(fb.bar)
FROM foo_bar fb, calendar_dim c
fb.calendar_id = c.calendar_id AND
c.year = 2006 AND
c.quarter = 1