System Calendar - SYS_CALENDAR.CALENDAR

• The view SYS_CALENDAR.CALENDAR is the system calendar used in TD.

• This calendar has one row for every day from 1st Jan 1900 to 31st Dec 2100.

Select count(*) from  SYS_CALENDAR.CALENDAR --> 73414

• Following are the columns available in the calendar view:

Column	Description.	Example
calendar_date	DATE   , Default DATE format, values 1900/01/01 - 2100/12/31 is the primary key	3/18/2013
day_of_week	INTEGER, 1 (Sunday) - 7 (Saturday)	2
day_of_month	INTEGER, 1 - 31	18
day_of_year	INTEGER, 1 - 366	77
day_of_calendar	INTEGER, indicating number of days since and including 1900/01/01	41350
weekday_of_month	INTEGER, 1 - 5, the nth occurrence of the weekday in the month	3
week_of_month	INTEGER, 0 - 5, the nth full week of month, first partial week is 0	3
week_of_year	INTEGER, 0 - 53, the nth full week of year, first partial week is 0	11
week_of_calendar	INTEGER, indicating the nth full week of calendar, the first partial week is 0	5907
month_of_quarter	INTEGER, 1 - 3	3
month_of_year	INTEGER, 1 (January) - 12 (December)	3
month_of_calendar	INTEGER, indicating number of months since and including 1900/01	1359
quarter_of_year	INTEGER, 1 (Jan/Feb/Mar) - 4 (Oct/Nov/Dec)	1
quarter_of_calendar	INTEGER indicating number of quarters since and including 1900Q1	453
year_of_calendar	INTEGER indicating number of calendar years in 4 digit format	2013

• Sys calendar is materialized using set of high performance nested views, which provide us with detailed info about each date.

• SYS_CALENDAR.CALENDAR is actually a 4 level nested view. Each level adds intelligence to the date.

• Caldates is the base table on which the calendar intelligence is built.  This table has only one column cdate which contains the date.

show table sys_Calendar.caldates

CREATE SET TABLE sys_Calendar.caldates ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      cdate DATE FORMAT 'YYYY/MM/DD')

UNIQUE PRIMARY INDEX ( cdate );

• Following is how the sys_calendar is built:

CALENDAR(view)

^

|

|

CALENDARTMP(view)

^

|

|

CALBASICS(view)

^

|

|

CALDATES(table)

• Example of using Sys_calendar:

Assume you have a sales table and we are expected to find the sum of the sales for the 1st quarter of year 2013.

Following is how we would  query

SELECT SUM(SALES_AMT) FROM

SALES sal

JOIN

SYS_CALENDAR.CALENDAR cal

ON

sal.sale_date=cal.calendar_date

AND cal.year_of_calendar='2013'

AND cal.quarter_of_year=1;