Before you can use these functions, you must run the Database Initialization Program (DIP) utility and execute the DIPSYSFNC script. The DIPALL or DIPSYSFNC script will create the calendar functions in the TD_SYSFNLIB database. For more information about the DIP utility, see Teradata Vantageā¢ - Database Utilities, B035-1102.
If you have a UDF with the same name as a business calendar function, you must remove that UDF from the normal UDF search path before you can invoke the business calendar function. If the business calendar function is not found in the current database, Vantage searches for the function in the TD_SYSFNLIB database. Alternatively, you may invoke the calendar function by using the fully qualified syntax, TD_SYSFNLIB.calendar_function_name.
Example: Cumulative Sales During First Week of All Quarters
This example finds cumulative sales of an item during first week of all quarters. Results are shown for each calendar setting.
SEL Item_Code, SUM(Sale_Amt) FROM Sales_Tbl WHERE WeekNumber_Of_Quarter(Sale_Date) = 1 GROUP BY Item_Code;
SET SESSION calendar = Teradata;
Item_Code | SUM(Sale_Amt) |
101 | 225 |
102 | 120 |
SET SESSION calendar = Compatible
Item_Code | SUM(Sale_Amt) |
101 | 245 |
102 | 100 |
SET SESSION calendar = ISO;
Item_Code | SUM(Sale_Amt) |
101 | 135 |
102 | 30 |
Sales_Tbl | ||
Item_Code | Sale_Amt | Sale_Date |
101 | 10 | 2008-12-30 |
101 | 15 | 2008-12-31 |
101 | 20 | 2009-01-01 |
101 | 25 | 2009-01-02 |
101 | 30 | 2009-01-03 |
101 | 35 | 2009-01-04 |
101 | 40 | 2009-01-05 |
101 | 45 | 2009-01-06 |
101 | 50 | 2009-01-07 |
101 | 55 | 2009-01-08 |
102 | 10 | 2009-01-01 |
102 | 20 | 2009-01-03 |
102 | 30 | 2009-01-05 |
102 | 40 | 2009-01-07 |
102 | 50 | 2009-01-09 |
Jan 2009 Calendar | ||||||
S | M | T | W | T | F | S |
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 | 31 |