You are encouraged to define views on the Calendar system view because of its convenience.
A useful view to define on Calendar is Today:
CREATE VIEW Today AS ( SELECT * FROM Sys_Calendar.Calendar WHERE Sys_Calendar.Calendar.calendar_date = DATE );
The Calendar system view permits easy specification of arithmetic expressions and aggregation. This is particularly useful in online analytical processing environments, where requests commonly aggregate values by weeks, months, year-to-date, years, and so on. The following is an example.
What are the dollar sales for this week, last week, and the same weeks last year for all items in the sportswear department for women?
SELECT a2.week_of_calendar, SUM(a1.price) FROM Sales a1, CALENDAR a2, Item a3, Department a4, Today a5 WHERE a1.calendar_date=a2.calendar_date AND (a2.week_of_calendar=a5.week_of_calendar OR a2.week_of_calendar=a5.week_of_calendar - 1 OR a2.week_of_calendar=a5.week_of_calendar - 52 OR a2.week_of_calendar=a5.week_of_calendar - 53 ) AND a1.itemID=a3.itemID AND a3.classID=a4.classID AND a4.classDesc='Women’s Sportswear' GROUP BY a2.week_of_calendar ORDER BY a2.week_of_calendar;