P07ujjML

1DROP TABLE IF EXISTS `dates`;
2CREATE TABLE `dates` (
3  date_key int NOT NULL,
4  full_date date NULL,
5  day_name char(10) NOT NULL,
6  day_abbr char(3) NOT NULL,
7  day_of_month tinyint NOT NULL,
8  day_of_year smallint NOT NULL,
9  day_type char(10) NOT NULL,
10  week_day tinyint NOT NULL,
11  month_name char(10) NOT NULL,
12  month_abbr char(3) NOT NULL,
13  calendar_year smallint NOT NULL,
14  calendar_year_name CHAR(6) NOT NULL,
15  calendar_year_quarter char(10) NOT NULL,
16  calendar_year_month char(10) NOT NULL,
17  calendar_year_week char(10) NOT NULL,
18  calendar_quarter tinyint NOT NULL,
19  calendar_month tinyint NOT NULL,
20  calendar_week tinyint NOT NULL,
21  fiscal_year int NOT NULL,
22  fiscal_year_name CHAR(6) NOT NULL,
23  fiscal_year_quarter char(10) NOT NULL,
24  fiscal_year_month char(10) NOT NULL,
25  fiscal_year_week char(10) NOT NULL,
26  fiscal_quarter tinyint NOT NULL,
27  fiscal_month tinyint NOT NULL,
28  fiscal_week tinyint NOT NULL,
29  PRIMARY KEY (`date_key`),
30  KEY `idx_full_date` (`full_date`),
31  KEY `idx_week_day` (`week_day`),
32  KEY `idx_calendar_year` (`calendar_year`),
33  KEY `idx_fiscal_year` (`fiscal_year`),
34  KEY `idx_calendar_year_month` (`calendar_year_month`),
35  KEY `idx_fiscal_year_month` (`fiscal_year_month`)
36) ENGINE=InnoDB DEFAULT CHARSET=utf8;
37
38DELIMITER //
39DROP PROCEDURE IF EXISTS PopulateDateDimension//
40
41CREATE PROCEDURE PopulateDateDimension(BeginDate DATETIME, EndDate DATETIME, FiscalYearMonthsOffset INT)
42BEGIN
43  # These two counters are used in our loop.
44  DECLARE DateCounter DATETIME;    #Current date in loop
45  DECLARE FiscalCounter DATETIME;  #Fiscal Year Date in loop
46  
47  # Start the counter at the begin date
48  SET DateCounter = BeginDate;
49
50  WHILE DateCounter <= EndDate DO
51    # Calculate the current Fiscal date as an offset of the current date in the loop
52    SET FiscalCounter = DATE_ADD(DateCounter, INTERVAL FiscalYearMonthsOffset MONTH);
53
54    # add a record into the date dimension table for this date
55    INSERT INTO `dates` (
56      date_key,
57      full_date,
58      day_name,
59      day_abbr,
60      day_of_month,
61      day_of_year,
62      day_type,
63      week_day,
64      month_name,
65      month_abbr,
66      calendar_year,
67      calendar_year_name,
68      calendar_year_quarter,
69      calendar_year_month,
70      calendar_year_week,
71      calendar_quarter,
72      calendar_month,
73      calendar_week,
74      fiscal_year,
75      fiscal_year_name,
76      fiscal_year_quarter,
77      fiscal_year_month,
78      fiscal_year_week,
79      fiscal_quarter,
80      fiscal_month,
81      fiscal_week
82    )
83    VALUES (
84      (YEAR(DateCounter)*10000)+(MONTH(DateCounter)*100)+DAY(DateCounter), # date_key
85      DateCounter, # full_date
86      DAYNAME(DateCounter), # day_name
87      DATE_FORMAT(DateCounter, '%a'), # day_abbr
88      DAYOFMONTH(DateCounter), # day_of_month
89      DAYOFYEAR(DateCounter), # day_of_year
90      CASE DAYNAME(DateCounter) 
91        WHEN 'Saturday' THEN 'Weekend'
92        WHEN 'Sunday' THEN 'Weekend'
93        ELSE 'Weekday'
94      END, # day_type
95      WEEKDAY(DateCounter), # day_week_day
96      MONTHNAME(DateCounter), # month_name
97      DATE_FORMAT(DateCounter, '%b'), # month_abbr
98      YEAR(DateCounter), # calendar_year
99      CONCAT('CY', YEAR(DateCounter)), # calendar_year_name
100      CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)), '-Q', QUARTER(DateCounter)), # calendar_year_quarter
101      CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)), '-', DATE_FORMAT(DateCounter,'%m')), # calendar_year_month
102      CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)), '-W', WEEKOFYEAR(DateCounter)), # calendar_year_week
103      QUARTER(DateCounter), # calendar_quarter
104      MONTH(DateCounter), # calendar_month
105      WEEKOFYEAR(DateCounter), # calendar_week
106      YEAR(FiscalCounter), # fiscal_year
107      CONCAT('FY', YEAR(FiscalCounter)), # fiscal_year_name
108      CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)), '-Q', QUARTER(FiscalCounter)), # fiscal_year_quarter
109      CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)), '-', DATE_FORMAT(FiscalCounter,'%m')), # fiscal_year_month
110      CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)), '-W', WEEKOFYEAR(FiscalCounter)), # fiscal_year_week
111      QUARTER(FiscalCounter), # fiscal_quarter
112      MONTH(FiscalCounter), # fiscal_month
113      WEEKOFYEAR(FiscalCounter) # fiscal_week
114    );
115
116    # Increment the date counter for next pass thru the loop
117    SET DateCounter = DATE_ADD(DateCounter, INTERVAL 1 DAY);
118  END WHILE;
119END//
120
121# Call by executing: CALL PopulateDateDimension('1970-01-01', '2050-12-31', 6);