What is a System Calendar?

• We can extend the properties of DATE by joining with the System Calendar.

• System Calendar could be joined with easily with other tables just as a dimension of a STAR schema.

• System Calendar does not need any manual maintenance. To render the benefits it has to offer all we need is to join with it.

• The System Calendar has one row for every day from January 1, 1900 through December 31, 2100.This means 200 years.  

• The System Calendar, as implemented for Teradata, is a high performance set of nested views.

• The System Calendar is actually a 4-level nested view. Each level of view built on top of the table adds intelligence to the date. 

• At the lowest level underlying table is Sys_calendar.Caldates.  Caldates has one column called "cdate" with a data type of DATE and is also the Unique primary index.  This has one row for each date in calendar.

•  Following are the columns that are accessible through system calendar.

calendar_date DATE UNIQUE (Standard Teradata date)
day_of_week BYTEINT, (1-7, where 1 = Sunday)
day_of_month BYTEINT, (1-31)
day_of_year SMALLINT, (1-366)
day_of_calendar INTEGER, (Julian days since 01/01/1900)
weekday_of_month BYTEINT, (nth occurrence of day in month)
week_of_month BYTEINT, (0-5)
week_of_year BYTEINT, (0-53) (partial week at start of year is 0)
week_of_calendar INTEGER, (0-n) (partial week at start is 0)
month_of_quarter BYTEINT, (1-3)
month_of_year BYTEINT, (1-12)
month_of_calendar INTEGER, (1-n) (Month number since Jan, 1900)
quarter_of_year BYTEINT, (1-4)
quarter_of_calendar INTEGER, (quarter number since Jan, 1900)
year_of_calendar SMALLINT, (year since 1900)

• Calendar is the view under Sys_calendar database that is generally used for complex date calculation. Calendar_date is the key column.

Example:

SELECT * FROM Sys_calendar.Calendar WHERE calendar_date = current_date;

calendar_date: 98/09/21
day_of_week: 2
day_of_month: 21
day_of_year: 264
day_of_calendar: 36058
weekday_of_month: 3
week_of_month: 3
week_of_year: 38
week_of_calendar: 5151
month_of_quarter: 3
month_of_year: 9
month_of_calendar: 1185
quarter_of_year: 3
quarter_of_calendar: 395
year_of_calendar: 1998

• ·         Example of using Sys_Calendar.Calendar for joining with other tables:

Find sum of sales in daily_sales table for the item id 10 in 1st Quarter of year 2011.

A:

Here we can do a normal sum on the sales column of the daily_sales table.

 However for additional logic of 1st quarter of 2011 we need to use System calendar.

All we need to do is join the date column in daily_sales table with the Calendar_date 

column in the Calendar view and then add the additional filter clauses.

Following is how the query would look like.

Select item_id,SUM(sales) FROM daily_sales A,Sys_calendar.Calendar SC where

A.sale_date=SC.Calendar_date and

AND  sc.quarter_of_year = 1

AND sc.year_of_calendar = 2011
AND A.itemid = 10

GROUP BY 1;

• ·         What is a Today view:

Many a times we need to perform queries that are relative to today’s date.

Hence we create a view using Sys_calendar.Calendar view to return information

 relative to the current day, week, month, quarter or year.

Following is how the view would look like:

CREATE VIEW today AS (

            SELECT * FROM sys_calendar.calendar

            WHERE calendar.calendar_date=current_date);

Example:

Compare sales for item 10 during the current and previous month for both this 

year and last year. 

Answer: 

SELECT sc.year_of_calendar AS "year"

   ,sc.month_of_year AS "month"

   ,ds.itemid

   ,sum(ds.sales)

FROM sys_calendar.calendar sc

     ,daily_sales ds

     ,today  td

WHERE sc.calendar_date = ds.salesdate

AND  ((sc.month_of_calendar BETWEEN td.month_of_calendar - 1

            AND td.month_of_calendar)
OR     (sc.month_of_calendar BETWEEN td.month_of_calendar - 13

            AND td.month_of_calendar - 12))

AND ds.itemid = 10

GROUP BY 1,2,3

ORDER BY 1,2;

• ·         There is a Difference in which the column ‘week_of_month’ works. 

  The System Calendar identifies each week of the month with a number

  from 0 to 5, with 0, if present, indicating the first partial week of the month.

   Week number 1 will always be the first full week of the month.

• · Day of week is assigned values 1 to 7 representing each day of the week 

  where Sunday is day one.