Scribd
Upload
18 views2 pages

Teradata System - Calendar Examples and Teradata Faqs On System Calendar

The document provides examples of using the Teradata SYSTEM_CALENDAR to retrieve date-related information from a SALES table for analysis. It shows functions like CALENDAR_DATE, DAY_OF_WEEK, MONTH_OF_YEAR, QUARTER_OF_YEAR that can be used to filter sales data by date and calculate aggregations like sums of SALESINCOME by month, quarter, year through joins with the SYSTEM_CALENDAR table. Queries are demonstrated to retrieve sales for the current date, current quarter, current month and previous months from the last 1-2 years.

Uploaded by

rameshsamarla
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
18 views2 pages

Teradata System - Calendar Examples and Teradata Faqs On System Calendar

The document provides examples of using the Teradata SYSTEM_CALENDAR to retrieve date-related information from a SALES table for analysis. It shows functions like CALENDAR_DATE, DAY_OF_WEEK, MONTH_OF_YEAR, QUARTER_OF_YEAR that can be used to filter sales data by date and calculate aggregations like sums of SALESINCOME by month, quarter, year through joins with the SYSTEM_CALENDAR table. Queries are demonstrated to retrieve sales for the current date, current quarter, current month and previous months from the last 1-2 years.

Uploaded by

rameshsamarla
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

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

You might also like