DROP TABLE IF EXISTS `dates`;
CREATE TABLE `dates` (
  date_key int NOT NULL,
  full_date date NULL,
  day_name char(10) NOT NULL,
  day_abbr char(3) NOT NULL,
  day_of_month tinyint NOT NULL,
  day_of_year smallint NOT NULL,
  day_type char(10) NOT NULL,
  week_day tinyint NOT NULL,
  month_name char(10) NOT NULL,
  month_abbr char(3) NOT NULL,
  calendar_year smallint NOT NULL,
  calendar_year_name CHAR(6) NOT NULL,
  calendar_year_quarter char(10) NOT NULL,
  calendar_year_month char(10) NOT NULL,
  calendar_year_week char(10) NOT NULL,
  calendar_quarter tinyint NOT NULL,
  calendar_month tinyint NOT NULL,
  calendar_week tinyint NOT NULL,
  fiscal_year int NOT NULL,
  fiscal_year_name CHAR(6) NOT NULL,
  fiscal_year_quarter char(10) NOT NULL,
  fiscal_year_month char(10) NOT NULL,
  fiscal_year_week char(10) NOT NULL,
  fiscal_quarter tinyint NOT NULL,
  fiscal_month tinyint NOT NULL,
  fiscal_week tinyint NOT NULL,
  PRIMARY KEY (`date_key`),
  KEY `idx_full_date` (`full_date`),
  KEY `idx_week_day` (`week_day`),
  KEY `idx_calendar_year` (`calendar_year`),
  KEY `idx_fiscal_year` (`fiscal_year`),
  KEY `idx_calendar_year_month` (`calendar_year_month`),
  KEY `idx_fiscal_year_month` (`fiscal_year_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER //
DROP PROCEDURE IF EXISTS PopulateDateDimension//

CREATE PROCEDURE PopulateDateDimension(BeginDate DATETIME, EndDate DATETIME, FiscalYearMonthsOffset INT)
BEGIN
  # These two counters are used in our loop.
  DECLARE DateCounter DATETIME;    #Current date in loop
  DECLARE FiscalCounter DATETIME;  #Fiscal Year Date in loop

  # Start the counter at the begin date
  SET DateCounter = BeginDate;

  WHILE DateCounter <= EndDate DO
    # Calculate the current Fiscal date as an offset of the current date in the loop
    SET FiscalCounter = DATE_ADD(DateCounter, INTERVAL FiscalYearMonthsOffset MONTH);

    # add a record into the date dimension table for this date
    INSERT INTO `dates` (
      date_key,
      full_date,
      day_name,
      day_abbr,
      day_of_month,
      day_of_year,
      day_type,
      week_day,
      month_name,
      month_abbr,
      calendar_year,
      calendar_year_name,
      calendar_year_quarter,
      calendar_year_month,
      calendar_year_week,
      calendar_quarter,
      calendar_month,
      calendar_week,
      fiscal_year,
      fiscal_year_name,
      fiscal_year_quarter,
      fiscal_year_month,
      fiscal_year_week,
      fiscal_quarter,
      fiscal_month,
      fiscal_week
    )
    VALUES (
      (YEAR(DateCounter)*10000)+(MONTH(DateCounter)*100)+DAY(DateCounter), # date_key
      DateCounter, # full_date
      DAYNAME(DateCounter), # day_name
      DATE_FORMAT(DateCounter, '%a'), # day_abbr
      DAYOFMONTH(DateCounter), # day_of_month
      DAYOFYEAR(DateCounter), # day_of_year
      CASE DAYNAME(DateCounter)
        WHEN 'Saturday' THEN 'Weekend'
        WHEN 'Sunday' THEN 'Weekend'
        ELSE 'Weekday'
      END, # day_type
      WEEKDAY(DateCounter), # day_week_day
      MONTHNAME(DateCounter), # month_name
      DATE_FORMAT(DateCounter, '%b'), # month_abbr
      YEAR(DateCounter), # calendar_year
      CONCAT('CY', YEAR(DateCounter)), # calendar_year_name
      CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)), '-Q', QUARTER(DateCounter)), # calendar_year_quarter
      CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)), '-', DATE_FORMAT(DateCounter,'%m')), # calendar_year_month
      CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)), '-W', WEEKOFYEAR(DateCounter)), # calendar_year_week
      QUARTER(DateCounter), # calendar_quarter
      MONTH(DateCounter), # calendar_month
      WEEKOFYEAR(DateCounter), # calendar_week
      YEAR(FiscalCounter), # fiscal_year
      CONCAT('FY', YEAR(FiscalCounter)), # fiscal_year_name
      CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)), '-Q', QUARTER(FiscalCounter)), # fiscal_year_quarter
      CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)), '-', DATE_FORMAT(FiscalCounter,'%m')), # fiscal_year_month
      CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)), '-W', WEEKOFYEAR(FiscalCounter)), # fiscal_year_week
      QUARTER(FiscalCounter), # fiscal_quarter
      MONTH(FiscalCounter), # fiscal_month
      WEEKOFYEAR(FiscalCounter) # fiscal_week
    );

    # Increment the date counter for next pass thru the loop
    SET DateCounter = DATE_ADD(DateCounter, INTERVAL 1 DAY);
  END WHILE;
END//

# Call by executing: CALL PopulateDateDimension('1970-01-01', '2050-12-31', 6);