TERADATA SYSTEM_CALENDAR EXAMPLES AND TERADATA FAQS ON SYSTEM CALENDAR

CALENDAR_DATE-->STANDARD DATE
DAY_OF_WEEK-->(1-7,1=SUNDAY)
DAY_OF_MONTH-->(1-31)
DAY_OF_YEAR-->(1-366)
WEEK_OF_MONTH-->(1-5)
WEEK-OF_YEAR-->1-52
MONTH_OF_QUARTER--->(1-3)
MONTH_OF_YEAR-->(1-12)
QUARTER_OF_YEAR-->(1-4)
DAY_OF_CALENDAR
MONTH_OF_CALDAR
YEAR_OF_CALENDAR
QUARTER_OF_CALENDAR
WEEK_OF_CALENDAR

CREATE MULTISET TABLE SALES(ID INTEGER,SALESDATE
DATE FORMAT 'YYYY-MM-DD',SALESINCOME INTEGER)
INSERT INTO SALES(1,'2009-10-01',1000);
INSERT INTO SALES(1,'2010-10-01',1000);
INSERT INTO SALES(1,'2009-10-02',1000);
INSERT INTO SALES(1,'2010-10-02',1000);
INSERT INTO SALES(1,'2009-09-01',1000);
INSERT INTO SALES(1,'2010-09-01',1000);
INSERT INTO SALES(1,'2009-09-02',1000);
INSERT INTO SALES(1,'2010-09-02',1000);
---
display current day info from the table
==========================================
SEL * FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE
==
display 2ND QUARTER OF 2014 SALES INFORMATION
==============================================
SEL S.ID,SUM(S.SALESINCOME) FROM SALES S,SYS_CALENDAR.CALENDAR C
WHERE S.SALESDATE=C.CALENDAR_DATE
AND C.QUARTER_OF_YEAR=2
AND C.YEAR_OF_CALENDAR=2014
GROUP BY 1
----
DISPLAY 3 RD MONTH SALES IN 2014
---------------------------------------------------------
IN THE ABOVE QUERY
AND C.MONTH_OF_YEAR=3
AND C.YEAR_OF_CALENDAR=2014
-----------------

DISPLAY CURRENT MONTH SALES (WITHOUT HARD CODDING)
=======================================================
SEL S.ID,SUM(S.SALESINCOME) FROM SALES S,SYS_CALENDAR.CALENDAR C,
(SEL * FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)D
WHERE S.SALESDATE=C.CALENDAR_DATE
AND C.MONTH_OF_CALENDAR=D.MONTH_OF_CALENDAR
GROUP BY 1


DISPLAY CURRENT YEAR CURRENT MONTH,PREVIOUS MONTH AND LAST YEAR
CURRENT MONTH AND PREVIOUS MONTH SALES INFO
================================================
SEL S.ID,C.YEAR_OF_CALENDAR,C.MONTH_OF_YEAR,
SUM(S.SALESINCOME) FROM SALES S,SYS_CALENDAR.CALENDAR C,
(SEL * FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)D
WHERE S.SALESDATE=C.CALENDAR_DATE
AND
(
C.MONTH_OF_CALENDAR=D.MONTH_OF_CALENDAR
OR
C.MONTH_OF_CALENDAR=D.MONTH_OF_CALENDAR-1
OR
C.MONTH_OF_CALENDAR=D.MONTH_OF_CALENDAR-12
OR
C.MONTH_OF_CALENDAR=D.MONTH_OF_CALENDAR-13
)
GROUP BY 1,2,3