I need to create one table called weeks that have all week numbers in it with extra info so we can query properly, but I want to add 2 new fields the week_start and week_end date stating when that week start and end. When we use the week() function in mysql we can pass one extra parameter to tell when the week starts in order to get the week number, here I want to do the same to find out the week date from week number so we can populate those 2 fields based on what the user needs. Lets say we have:
year: 2019 week: 25
I want to find out what is the date when starts and ends but I want to pass the extra parameter 1, 2 just like when finding out the week number, for example: week('2019-11-28', 1) = 48, so in order to do the reverse way and find out the first date of that week 52 we also need to pass the second parameter stating when the week start. That is where I need some help to make this right for everbody.
Thanks
DROP procedure IF EXISTS `populate_weeks_table`;
DELIMITER $$
USE `whb_staging`$$
CREATE PROCEDURE `populate_weeks_table` ()
BEGIN
DECLARE year_start INT;
DECLARE year_end INT;
DECLARE weekcount INT;
SET year_start = year(now()) - 60;
SET year_end = year(now()) + 60;
SET weekcount = 1;
CREATE TABLE `weeks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`yearweek_num` int(11) DEFAULT NULL,
`week_year` int(11) DEFAULT NULL,
`week_num` int(11) DEFAULT NULL,
`week_start` date DEFAULT NULL,
`week_end` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `yearweek` (`yearweek_num`),
KEY `wyear_wnum` (`week_year`,`week_num`),
KEY `wstart_wend` (`week_start`,`week_end`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
WHILE year_start <= year_end DO
WHILE weekcount <= 52 DO
insert into weeks(`yearweek_num`,`week_year`,`week_num`,`week_start`,`week_end`)
values(concat(year_start, weekcount), year_start, weekcount, '2019-01-01', '2019-01-01');
SET weekcount = weekcount + 1;
END WHILE;
SET year_start = year_start + 1;
SET weekcount = 1;
END WHILE;
END$$
DELIMITER ;