Sys_Calendar.Calendar Example | Teradata Vantage - Example: Using Sys_Calendar.Calendar - Advanced SQL Engine - Teradata Database

Data Dictionary

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-10-15
dita:mapPath
yoz1556235523563.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1092
lifecycle
previous
Product Category
Teradata Vantage™

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;