Teradata Date Dimension

In our project, we needed a date dimension. I did a quick check on-line, but most of the examples were not specific to Teradata.

Teradata has views and an underlying table that made this relatively easy:

• sys_calendar.CALENDAR: This is just a simple view on top of the CALENDARTMP view.
• sys_calendar.CALENDARTMP: This is a set of calculations on top of the CALBASICS view.
• sys_calendar.CALBASICS: This is a set of calculations on top of the CALDATES table.
• sys_calendar.CALDATES: This is a single-column table (cdate) with 73,414 rows and dates from 01/01/1900 to 12/31/2100.

Here is the actual code I used. I'm sure there are more elegant ways to pull this data, but this was straightforward and maintainable.

(Code updated 2010-12-16. All columns are now NOT NULL, and calculation for week_end_dt has been updated to deal with last six days of internal calendar.)

DROP TABLE leap;
CREATE VOLATILE TABLE leap (
    leap_year_nbr   INT
) ON COMMIT PRESERVE ROWS;

INSERT INTO leap (leap_year_nbr)
SELECT year_of_calendar
FROM sys_calendar.CALENDAR
WHERE month_of_year = 2
  AND day_of_month  = 29;

--SELECT * FROM leap;
--SELECT COUNT(*) AS LeapCt FROM leap;

-- sys_calendar.calendar
-- 73,414 entries (01/01/1900 to 12/31/2100)
-- ================================================
-- date_dim
-- ================================================
DROP TABLE date_dim;
CREATE TABLE date_dim (
    calendar_dt                     date                        NOT NULL,
    year_nbr                        smallint                    NOT NULL,
    day_of_week_nbr                 byteint                     NOT NULL,
    day_of_month_nbr                byteint                     NOT NULL,
    day_of_year_nbr                 smallint                    NOT NULL,
    week_of_month_nbr               byteint                     NOT NULL,
    week_of_year_nbr                byteint                     NOT NULL,
    month_of_quarter                byteint                     NOT NULL,
    month_of_year                   byteint                     NOT NULL,
    month_of_calendar               smallint                    NOT NULL,
    quarter_of_year                 byteint                     NOT NULL,
    day_of_week_nm                  CHAR(9)                     NOT NULL,
    day_of_week_abbr2               CHAR(2)                     NOT NULL,
    day_of_week_abbr3               CHAR(3)                     NOT NULL,
    month_nm                        CHAR(15)                    NOT NULL,
    month_abbr2                     CHAR(2)                     NOT NULL,
    month_abbr3                     CHAR(3)                     NOT NULL,
    week_end_dt                     date                        NOT NULL,
    end_of_month_flag               CHAR(1)                     NOT NULL,
    first_of_quarter_flag           CHAR(1)                     NOT NULL,
    end_of_quarter_flag             CHAR(1)                     NOT NULL,
    end_of_year_flag                CHAR(1)                     NOT NULL,
    weekday_flag                    CHAR(1)                     NOT NULL,
    weekend_flag                    CHAR(1)                     NOT NULL,
    leap_year_flag                  CHAR(1)                     NOT NULL
) UNIQUE PRIMARY INDEX (calendar_dt);

INSERT INTO date_dim (
    calendar_dt
    ,year_nbr
    ,day_of_week_nbr
    ,day_of_month_nbr
    ,day_of_year_nbr
    ,week_of_month_nbr
    ,week_of_year_nbr
    ,month_of_quarter
    ,month_of_year
    ,month_of_calendar
    ,quarter_of_year
    ,day_of_week_nm
    ,day_of_week_abbr2
    ,day_of_week_abbr3
    ,month_nm
    ,month_abbr2
    ,month_abbr3
    ,week_end_dt
    ,end_of_month_flag
    ,first_of_quarter_flag
    ,end_of_quarter_flag
    ,end_of_year_flag
    ,weekday_flag
    ,weekend_flag
    ,leap_year_flag
)
SELECT
    --TOP 1000
    calendar_date,
    year_of_calendar,
    day_of_week,
    day_of_month,
    day_of_year,
    week_of_month,
    week_of_year,
    month_of_quarter,
    month_of_year,
    month_of_calendar,
    quarter_of_year,
    CASE day_of_week            -- day_of_week_nm
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END,
    CASE day_of_week            -- day_of_week_abbr2
        WHEN 1 THEN 'Su'
        WHEN 2 THEN 'Mo'
        WHEN 3 THEN 'Tu'
        WHEN 4 THEN 'We'
        WHEN 5 THEN 'Th'
        WHEN 6 THEN 'Fr'
        WHEN 7 THEN 'Sa'
    END,
    CASE day_of_week            -- day_of_week_abbr3
        WHEN 1 THEN 'Sun'
        WHEN 2 THEN 'Mon'
        WHEN 3 THEN 'Tue'
        WHEN 4 THEN 'Wed'
        WHEN 5 THEN 'Thu'
        WHEN 6 THEN 'Fri'
        WHEN 7 THEN 'Sat'
    END,
    CASE month_of_year          -- month_nm
        WHEN  1 THEN 'January'
        WHEN  2 THEN 'February'
        WHEN  3 THEN 'March'
        WHEN  4 THEN 'April'
        WHEN  5 THEN 'May'
        WHEN  6 THEN 'June'
        WHEN  7 THEN 'July'
        WHEN  8 THEN 'August'
        WHEN  9 THEN 'September'
        WHEN 10 THEN 'October'
        WHEN 11 THEN 'November'
        WHEN 12 THEN 'December'
    END,
    CASE month_of_year          -- month_abbr2
        WHEN  1 THEN 'Ja'
        WHEN  2 THEN 'Fe'
        WHEN  3 THEN 'Mr'
        WHEN  4 THEN 'Ap'
        WHEN  5 THEN 'My'
        WHEN  6 THEN 'Jn'
        WHEN  7 THEN 'Jl'
        WHEN  8 THEN 'Au'
        WHEN  9 THEN 'Se'
        WHEN 10 THEN 'Oc'
        WHEN 11 THEN 'No'
        WHEN 12 THEN 'De'
    END,
    CASE month_of_year          -- month_abbr3
        WHEN  1 THEN 'Jan'
        WHEN  2 THEN 'Feb'
        WHEN  3 THEN 'Mar'
        WHEN  4 THEN 'Apr'
        WHEN  5 THEN 'May'
        WHEN  6 THEN 'Jun'
        WHEN  7 THEN 'Jul'
        WHEN  8 THEN 'Aug'
        WHEN  9 THEN 'Sep'
        WHEN 10 THEN 'Oct'
        WHEN 11 THEN 'Nov'
        WHEN 12 THEN 'Dec'
    END,
    CASE    -- week_end_dt
        WHEN calendar_date BETWEEN DATE '2100-12-26' AND DATE '2100-12-31' THEN DATE '2101-01-01'   -- For these dates, the calculations run off the end of the internal calendar
        WHEN day_of_week = 1 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 6)
        WHEN day_of_week = 2 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 5)
        WHEN day_of_week = 3 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 4)
        WHEN day_of_week = 4 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 3)
        WHEN day_of_week = 5 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 2)
        WHEN day_of_week = 6 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 1)
        WHEN day_of_week = 7 THEN calendar_date
        ELSE DATE '9999-12-31'
    END,
    CASE    -- end_of_month_flag
        WHEN month_of_year =  1 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  2 AND (l.leap_year_nbr IS NOT NULL AND day_of_month = 29) THEN 'Y'
        WHEN month_of_year =  2 AND (l.leap_year_nbr IS     NULL AND day_of_month = 28) THEN 'Y'
        WHEN month_of_year =  3 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  4 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year =  5 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  6 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year =  7 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  8 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  9 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year = 10 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year = 11 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year = 12 AND day_of_month = 31 THEN 'Y'
        ELSE 'N'
    END,
    CASE    -- first_of_quarter_flag
        WHEN month_of_year =  1 AND day_of_month = 1 THEN 'Y'
        WHEN month_of_year =  4 AND day_of_month = 1 THEN 'Y'
        WHEN month_of_year =  7 AND day_of_month = 1 THEN 'Y'
        WHEN month_of_year = 10 AND day_of_month = 1 THEN 'Y'
    ELSE 'N' END,
    CASE    -- end_of_quarter_flag
        WHEN month_of_year =  3 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  6 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year =  9 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year = 12 AND day_of_month = 31 THEN 'Y'
    ELSE 'N' END,
    CASE WHEN month_of_year = 12 AND day_of_month = 31 THEN 'Y' ELSE 'N' END,   -- end_of_year_flag
    CASE day_of_week WHEN 1 THEN 'N' WHEN 7 THEN 'N' ELSE 'Y' END,              -- weekday_flag
    CASE day_of_week WHEN 1 THEN 'Y' WHEN 7 THEN 'Y' ELSE 'N' END,              -- weekend_flag
    CASE    -- leap_year_flag
        WHEN l.leap_year_nbr IS NOT NULL THEN 'Y' ELSE 'N'
    END
FROM sys_calendar.CALENDAR  AS c
LEFT OUTER JOIN leap        AS l ON c.year_of_calendar = l.leap_year_nbr
;