SYS_CALENDAR in Teradata

The SYS_CALENDAR.CALENDAR system view helps to extend the properties of a DATE data type column by means of a join.
The CALENDAR system view is defined for the years 1900 - 2100.
To access SYS_CALENDAR.CALENDAR, you must have SELECT privileges.

How to Use CALENDAR ?

Please find below the detailed example to know that.

1. Create a view TODAY (Useful view for date manipulations)

CREATE VIEW Today AS (
SELECT *
FROM SYS_CALENDAR.Calendar
WHERE SYS_CALENDAR.Calendar.calendar_date = DATE
);

What are the dollar sales for this week, last week, and the same weeks last year
for all items in the cosmetics class for women?

Consider the following tables to illustrate the example,
Sales - The basic table which has all the details of sales
SYS_CALENDAR.CALENDAR - System view of calendar
Item - To store specific items
Class - Item's class specific. (Ex. Cosmetics, footwear, sports)
Today - View that is defined above.

SELECT a2.week_of_calendar, SUM(a1.price)
FROM Sales a1, SYS_CALENDAR.CALENDAR a2, Item a3, Class 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 cosmetics’
GROUP BY a2.week_of_calendar
ORDER BY a2.week_of_calendar

The above code explains how we can use the system calendar efficiently.
For getting the dollar price for a particular week sale, SUM(price) from sales table is rendered.
The weeks of Today and sys_calendar are compared to get the desired results.
a2.week_of_calendar=a5.week_of_calendar --> For the present week
a2.week_of_calendar=a5.week_of_calendar - 1 --> For the previous week
a2.week_of_calendar=a5.week_of_calendar - 52 --> Last year's week
OR a2.week_of_calendar=a5.week_of_calendar - 53 --> Previous week of Last year's week