Sys_Calendar.Calendar Example | Teradata Vantage - 17.00 - Example: Using Sys_Calendar.Calendar - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

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;