u187YrEr

1drop table if exists dim_date;
2CREATE TABLE dim_date(
3 date_key int NOT NULL,
4 full_date date NULL,
5 date_name char(11) NOT NULL,
6 date_name_us char(11) NOT NULL,
7 date_name_eu char(11) NOT NULL,
8 day_of_week tinyint NOT NULL,
9 day_name_of_week char(10) NOT NULL,
10 day_of_month tinyint NOT NULL,
11 day_of_year smallint NOT NULL,
12 weekday_weekend char(10) NOT NULL,
13 week_of_year tinyint NOT NULL,
14 month_name char(10) NOT NULL,
15 month_of_year tinyint NOT NULL,
16 is_last_day_of_month char(1) NOT NULL,
17 calendar_quarter tinyint NOT NULL,
18 calendar_year smallint NOT NULL,
19 calendar_year_month char(10) NOT NULL,
20 calendar_year_qtr char(10) NOT NULL,
21 fiscal_month_of_year tinyint NOT NULL,
22 fiscal_quarter tinyint NOT NULL,
23 fiscal_year int NOT NULL,
24 fiscal_year_month char(10) NOT NULL,
25 fiscal_year_qtr char(10) NOT NULL,
26  PRIMARY KEY (`date_key`)
27) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
28
29
30delimiter //
31
32drop procedure if exists PopulateDateDimension//
33
34CREATE PROCEDURE PopulateDateDimension(BeginDate DATETIME, EndDate DATETIME)
35BEGIN
36
37
38 DECLARE LastDayOfMon CHAR(1);
39 DECLARE FiscalYearMonthsOffset INT;
40 
41 DECLARE DateCounter DATETIME;    #Current date in loop
42 DECLARE FiscalCounter DATETIME;  #Fiscal Year Date in loop
43
44 # Set this to the number of months to add to the current date to get
45 # the beginning of the Fiscal year. For example, if the Fiscal year
46 # begins July 1, put a 6 there.
47 # Negative values are also allowed, thus if your 2010 Fiscal year
48 # begins in July of 2009, put a -6.
49 SET FiscalYearMonthsOffset = 6;
50
51 # Start the counter at the begin date
52 SET DateCounter = BeginDate;
53
54 WHILE DateCounter <= EndDate DO
55            # Calculate the current Fiscal date as an offset of
56            # the current date in the loop
57
58            SET FiscalCounter = DATE_ADD(DateCounter, INTERVAL FiscalYearMonthsOffset MONTH);
59
60            # Set value for IsLastDayOfMonth
61            IF MONTH(DateCounter) = MONTH(DATE_ADD(DateCounter, INTERVAL 1 DAY)) THEN
62               SET LastDayOfMon = 'N';
63            ELSE
64               SET LastDayOfMon = 'Y';
65   END IF;
66
67            # add a record into the date dimension table for this date
68            INSERT  INTO dim_date
69       (date_key
70       ,full_date
71       ,date_name
72       ,date_name_us
73       ,date_name_eu
74       ,day_of_week
75       ,day_name_of_week
76       ,day_of_month
77       ,day_of_year
78       ,weekday_weekend
79       ,week_of_year
80       ,month_name
81       ,month_of_year
82       ,is_last_day_of_month
83       ,calendar_quarter
84       ,calendar_year
85       ,calendar_year_month
86       ,calendar_year_qtr
87       ,fiscal_month_of_year
88       ,fiscal_quarter
89       ,fiscal_year
90       ,fiscal_year_month
91       ,fiscal_year_qtr)
92            VALUES  (
93                      ( YEAR(DateCounter) * 10000 ) + ( MONTH(DateCounter)
94                                                         * 100 )
95                      + DAY(DateCounter)  #DateKey
96                    , DateCounter # FullDate
97                    , CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)),'/',DATE_FORMAT(DateCounter,'%m'),'/',DATE_FORMAT(DateCounter,'%d')) #DateName
98                    , CONCAT(DATE_FORMAT(DateCounter,'%m'),'/',DATE_FORMAT(DateCounter,'%d'),'/',CAST(YEAR(DateCounter) AS CHAR(4)))#DateNameUS
99                    , CONCAT(DATE_FORMAT(DateCounter,'%d'),'/',DATE_FORMAT(DateCounter,'%m'),'/',CAST(YEAR(DateCounter) AS CHAR(4)))#DateNameEU
100                    , DAYOFWEEK(DateCounter) #DayOfWeek
101                    , DAYNAME(DateCounter) #DayNameOfWeek
102                    , DAYOFMONTH(DateCounter) #DayOfMonth
103                    , DAYOFYEAR(DateCounter) #DayOfYear
104                    , CASE DAYNAME(DateCounter)
105                        WHEN 'Saturday' THEN 'Weekend'
106                        WHEN 'Sunday' THEN 'Weekend'
107                        ELSE 'Weekday'
108                      END #WeekdayWeekend
109                    , WEEKOFYEAR(DateCounter) #WeekOfYear
110                    , MONTHNAME(DateCounter) #MonthName
111                    , MONTH(DateCounter) #MonthOfYear
112                    , LastDayOfMon #IsLastDayOfMonth
113                    , QUARTER(DateCounter) #CalendarQuarter
114                    , YEAR(DateCounter) #CalendarYear
115                    , CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)),'-',DATE_FORMAT(DateCounter,'%m')) #CalendarYearMonth
116                    , CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)),'Q',QUARTER(DateCounter)) #CalendarYearQtr
117                    , MONTH(FiscalCounter) #[FiscalMonthOfYear]
118                    , QUARTER(FiscalCounter) #[FiscalQuarter]
119                    , YEAR(FiscalCounter) #[FiscalYear]
120                    , CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)),'-',DATE_FORMAT(FiscalCounter,'%m')) #[FiscalYearMonth]
121                    , CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)),'Q',QUARTER(FiscalCounter)) #[FiscalYearQtr]
122                    );
123
124            # Increment the date counter for next pass thru the loop
125            SET DateCounter = DATE_ADD(DateCounter, INTERVAL 1 DAY);
126      END WHILE;
127
128
129END// 
130
131CALL PopulateDateDimension('2010/01/01', '2025/12/31');