DROP TABLE IF EXISTS prAddVote;
DELIMITER
DROP TRIGGER IF EXISTS updateVoteMasterVotSum;
DELIMITER
DROP TABLE IF EXISTS DateLookup;
CREATE TABLE DateLookup
(
DateKey INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
DateFull DATETIME,
CharacterDate VARCHAR(10),
FullYear CHAR(4),
QuarterNumber TINYINT,
WeekNumber TINYINT,
WeekDayName VARCHAR(10),
MonthDay TINYINT,
MONTHNAME VARCHAR(12),
YearDay SMALLINT,
DateDefinition VARCHAR(30),
WEEKDAY TINYINT,
MonthNumber TINYINT
);
SET @Date = DATE_FORMAT(‘2019-12-01’,’%Y-%c-%d %h:%i:%s’);
DROP PROCEDURE IF EXISTS update_datelookup;
DELIMITER ##
CREATE PROCEDURE update_datelookup()
BEGIN
loop1: WHILE @Date < ‘2020-01-01 00:00:00’ DO
INSERT INTO DateLookup
(
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MONTHNAME, YearDay,
DateDefinition, CharacterDate,
WEEKDAY,MonthNumber
)
SELECT
UNIX_TIMESTAMP(@Date), @Date, YEAR(@Date),
QUARTER(@Date), WEEK(@Date), DAYOFWEEK(@Date),
DAYOFMONTH(@Date), MONTHNAME(@Date), DAYOFYEAR(@Date),
DATE_FORMAT(@Date,’%Y-%c-%d %h:%i:%s’),DATE_FORMAT(@Date,’%Y-%m-%d’),
WEEKDAY(@Date),
MONTH(@Date);
SET @Date = DATE_ADD(@Date,INTERVAL 1 DAY) ;
END WHILE loop1;
END ##
CALL update_datelookup();