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

Teradata Vantage™ - Data Dictionary

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

You are encouraged to define views on the Calendar system view because of its convenience.

A useful view to define on Calendar is Today:

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;