16.20 - Example: Using Sys_Calendar.Calendar - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ Data Dictionary

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
zzu1512081120577.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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;