Archives of the TeradataForum

Message Posted: Thu, 02 Sep 2004 @ 08:31:45 GMT


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

Subj:		Re: Generating week dates without sys_calendar

From:		Dieter Noeth

Dennis Calkins wrote:

I like the last post. It points out that SYS_CALENDAR is not complete and maybe it could be improved upon.

I guess the question would be would it be better to add extra columns to the current existing view

          SYS_CALENDAR.CALENDAR

or would it be simpler to have a separate view which uses the European or ISO definitions of YEAR START, MONTH START, WEEK START?

That's my calendar version, it includes ISO weeks and may start at any date (not only 1900-01-01)

Dieter

     /*
     ** Modified version of sys_calendar.calendar
     ** including week numbers according to ISO:
     **   - a week starts at monday
     **   - there's no week #0
     **   - week #1 contains the first thursday of the year
     **
     ** e.g.
     ** 2003-12-28 day 7 in week 2003W52
     ** 2003-12-29 day 1 in week 2004W01
     ** 2003-12-28 day 7 in week 2003W52
     ** 2003-12-29 day 1 in week 2004W01
     ** 2005-01-02 day 7 in week 2004W53
     ** 2005-01-03 day 1 in week 2005W01
     **
     **
     **  2003-03-12 initial version dn
     **/


     CREATE DATABASE ISO_Calendar
     FROM DBC AS
        PERMANENT = 15e+06,
        SPOOL     = 15e+06,
        FALLBACK;

     DATABASE ISO_Calendar;

     create table CalDates
     (cdate date not null primary key);

     create volatile table vt_cal_temp (
        i integer not null primary key
     ) on commit preserve rows;

     INSERT INTO vt_cal_temp VALUES(0)
     ;INSERT INTO vt_cal_temp VALUES(1)
     ;INSERT INTO vt_cal_temp VALUES(2)
     ;INSERT INTO vt_cal_temp VALUES(3)
     ;INSERT INTO vt_cal_temp VALUES(4)
     ;INSERT INTO vt_cal_temp VALUES(5)
     ;INSERT INTO vt_cal_temp VALUES(6)
     ;INSERT INTO vt_cal_temp VALUES(7)
     ;INSERT INTO vt_cal_temp VALUES(8)
     ;INSERT INTO vt_cal_temp VALUES(9)
     ;

     INSERT INTO CalDates
     SELECT
        date '1900-01-01' +
        t1.i +
        t2.i * 10 +
        t3.i * 100 +
        t4.i * 1000 +
        t5.i * 10000 as cdate
     from vt_cal_temp t1, vt_cal_temp t2, vt_cal_temp t3,
        vt_cal_temp t4, vt_cal_temp t5
     where
        cdate <= date '2100-12-31'
     ;

     drop table vt_cal_temp;


     REPLACE VIEW CALBASICS as
     select

        date '1900-01-01' as start_of_calendar,

        cdate as calendar_date,

        ((cdate - date '0001-01-01') mod 7) + 1 (format '9') AS day_of_week,

        extract(day from cdate) (format '99') as day_of_month,

        cdate - ((extract(year from cdate) - 1900) * 10000 + 0101 (date)) + 1
     (format '9999') as day_of_year,

        cdate - start_of_calendar + 1 as day_of_calendar,

        (day_of_month - 1) / 7 + 1 (format '9') as weekday_of_month,

        (day_of_month - day_of_week + 6) / 7 (format '9') as week_of_month,

        (day_of_calendar - day_of_week + 6) / 7 as week_of_calendar,

        (month_of_year - 1) mod 3 + 1 as month_of_quarter,

        extract(month from cdate) as month_of_year,

        month_of_year + 12 * (year_of_calendar - extract(year from
     start_of_calendar)) as month_of_calendar,

        (month_of_year + 2) / 3 as quarter_of_year,

        (month_of_year + 2) / 3 + 4 * (year_of_calendar - extract(year from
     start_of_calendar)) as quarter_of_calendar,

        extract(year from cdate) as year_of_calendar,

        case day_of_week
          when 1 then 'Monday'
          when 2 then 'Tuesday'
          when 3 then 'Wednesday'
          when 4 then 'Thursday'
          when 5 then 'Friday'
          when 6 then 'Saturday'
          when 7 then 'Sunday'
          else ''
        end as weekday,

        /**ISO_temp: this week's thursday**/
        cDate - Day_Of_Week + 4 AS ISO_temp,

        (extract (year from ISO_temp) (format '9999')) || 'W' ||
        (((ISO_temp - ((extract(year from ISO_temp) - 1900) * 10000 + 0101 (date))) / 7) + 1 (format '99'))
          as ISO_Week

     from caldates
     ;

     REPLACE VIEW Calendar as
     select
        calendar_date,
        day_of_week,
        day_of_month,
        day_of_year,
        day_of_calendar,
        weekday_of_month,
        week_of_month,
        week_of_calendar,
        ISO_Week,
        month_of_quarter,
        month_of_year,
        month_of_calendar,
        year_of_calendar,
        quarter_of_year,
        quarter_of_calendar
     from calbasics
     ;


     GRANT SELECT ON ISO_Calendar.Calendar TO PUBLIC;

     sel * from calendar
     where calendar_date = current_date;


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

Attachments

Library

Quick Reference