hXPHwXTv

1-- Creates a calendar table in mysql
2
3DROP TABLE IF EXISTS calendar;
4CREATE TABLE calendar (
5        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
6        db_date                 DATE NOT NULL,
7        year                    INTEGER NOT NULL,
8        month                   INTEGER NOT NULL, -- 1 to 12
9        day                     INTEGER NOT NULL, -- 1 to 31
10        quarter                 INTEGER NOT NULL, -- 1 to 4
11        week                    INTEGER NOT NULL, -- 1 to 52/53
12        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
13        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
14        holiday_flag            TINYINT(1) DEFAULT 0 CHECK (holiday_flag in (1, 0)),
15        weekend_flag            TINYINT(1) DEFAULT 0 CHECK (weekday_flag in (1, 0)),
16        event                   VARCHAR(50),
17        UNIQUE td_ymd_idx (year,month,day),
18        UNIQUE td_dbdate_idx (db_date)
19
20) Engine=innodb;
21
22DROP PROCEDURE IF EXISTS fill_date_dimension;
23DELIMITER //
24CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
25BEGIN
26    DECLARE currentdate DATE;
27    SET currentdate = startdate;
28    WHILE currentdate < stopdate DO
29        INSERT INTO calendar VALUES (
30                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
31                        currentdate,
32                        YEAR(currentdate),
33                        MONTH(currentdate),
34                        DAY(currentdate),
35                        QUARTER(currentdate),
36                        WEEKOFYEAR(currentdate),
37                        DATE_FORMAT(currentdate,'%W'),
38                        DATE_FORMAT(currentdate,'%M'),
39                        0,
40                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 1 WHEN 7 then 1 ELSE 0 END,
41                        NULL);
42        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
43    END WHILE;
44END
45//
46DELIMITER ;
47
48TRUNCATE TABLE calendar;
49
50CALL fill_date_dimension('1900-01-01','2200-01-01');
51
52DROP PROCEDURE IF EXISTS fill_date_dimension;