Archives of the TeradataForum

Message Posted: Sun, 23 Apr 2000 @ 18:48:54 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Yvonne/All

You may have noticed that there is a problem correctly representing quarter-years in view 'Sys_Calendar.Calendar'. The issue is that not all quarters have the same number of months. If memory serves, quarter 1 has three months, quarter 2 has four months, quarter three also has four months and quarter 4 has one - in all cases/years. I do not know if NCR has provided a patch for this, but if not, here's one possible solution.

The problem lies, as far as I can see, with the definition of view Sys_Calendar.CalendarTmp. This view defines quarter_of_month and quarter_of_calendar as follows:

     month_of_year / 4 + 1                         /* quarter_of_year     */
     month_of_year / 4 + 1 + 4 * year_of_calendar  /* quarter_of_calendar */

This gives rise to month 7 (July) being part of quarter 2 (7 \ 4 = 1 + 1 = 2 [where '\' = integer divide]), and month 11 (November) being part of quarter 3 (11 \ 4 = 2 + 1 = 3). Quarter four has only one month - December. Take a look at your Calendar view for any whole year to see this in action.

It is a trivial matter to correct this problem. On my demo version of Teradata for NT, I created two new views. The first, XCALENDARTMP, fixes the (imho) erroneous CALENDARTMP definitions.

     REPLACE VIEW SYS_CALENDAR.XCALENDARTMP(
       calendar_date,
       day_of_week,
       day_of_month,
       day_of_year,
       day_of_calendar,
       weekday_of_month,
       week_of_month,
       week_of_year,
       week_of_calendar,
       month_of_quarter,
       month_of_year,
       month_of_calendar,
       quarter_of_year,
       quarter_of_calendar,
       year_of_calendar)
     AS
     SEL
       calendar_date,
       (day_of_calendar + 0) mod 7 + 1,
       day_of_month,
       day_of_year,
       day_of_calendar,
       (day_of_month - 1) / 7 + 1,
       (day_of_month - (day_of_calendar + 0) mod 7 + 6) / 7,
       (day_of_year - (day_of_calendar + 0) mod 7 + 6) / 7,
       (day_of_calendar - (day_of_calendar + 0) mod 7 + 6) / 7,
       (month_of_year - 1) mod 3 + 1,
       month_of_year,
       month_of_year + 12 * year_of_calendar,
       ((month_of_year - 1) / 3) + 1,                       /* new qtr-of-year */
       ((month_of_year - 1) / 3) + 1 + 4 * year_of_calendar,/* new qtr-of-cal */
       year_of_calendar + 1900
     FROM SYS_CALENDAR.CALBASICS;

The second, XCALENDAR, emulates the existing CALENDAR view, but uses XCALENDARTMP as its data source:

     REPLACE VIEW SYS_CALENDAR.XCALENDAR(
       calendar_date,
       day_of_week,
       day_of_month,
       day_of_year,
       day_of_calendar,
       weekday_of_month,
       week_of_month,
       week_of_year,
       week_of_calendar,
       month_of_quarter,
       month_of_year,
       month_of_calendar,
       quarter_of_year,
       quarter_of_calendar,
       year_of_calendar)
     AS
     SEL
       calendar_date,
       day_of_week,
       day_of_month,
       day_of_year,
       day_of_calendar,
       weekday_of_month,
       week_of_month,
       week_of_year,
       week_of_calendar,
       month_of_quarter,
       month_of_year,
       month_of_calendar,
       quarter_of_year,
       quarter_of_calendar,
       year_of_calendar
     FROM SYS_CALENDAR.XCALENDARTMP;

After this, you will find by interrogating XCALENDAR that January-March are in quarter 1, April-June in quarter 2, July-September in Quarter 3 and October-December in quarter 4. The key to this is

((month_of_year - 1) / 3) + 1

which replaces the original quarter_of_year definition.

Hope this helps

Rgds
James Grogan

	<Prev	Next>		<<First	<Prev	Next>	Last>>