ine7synq

1DROP procedure IF EXISTS `populate_weeks_table`;
2
3DELIMITER $$
4USE `whb_staging`$$
5CREATE PROCEDURE `populate_weeks_table` ()
6BEGIN
7
8DECLARE year_start  INT;
9DECLARE year_end INT;
10DECLARE weekcount INT;
11SET year_start = year(now()) - 60;
12SET year_end   = year(now()) + 60;
13SET weekcount = 1;
14
15CREATE TABLE `weeks` (
16  `id` int(11) NOT NULL AUTO_INCREMENT,
17  `yearweek_num` int(11) DEFAULT NULL,
18  `week_year` int(11) DEFAULT NULL,
19  `week_num` int(11) DEFAULT NULL,
20  `week_start` date DEFAULT NULL,
21  `week_end` date DEFAULT NULL,
22  PRIMARY KEY (`id`),
23  KEY `yearweek` (`yearweek_num`),
24  KEY `wyear_wnum` (`week_year`,`week_num`),
25  KEY `wstart_wend` (`week_start`,`week_end`)
26) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
27
28WHILE year_start  <= year_end DO
29   WHILE weekcount <= 52 DO
30      insert into weeks(`yearweek_num`,`week_year`,`week_num`,`week_start`,`week_end`)
31             values(concat(year_start, weekcount), year_start, weekcount, '2019-01-01', '2019-01-01');
32      SET  weekcount = weekcount + 1; 
33   END WHILE;
34   SET  year_start = year_start + 1;
35   SET  weekcount = 1;
36END WHILE;
37
38END$$
39
40DELIMITER ;