0

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 ;
  • Sorry, I wrote week 52, but is week 48 as described above. – rafaelrglima Jun 16 '19 at 17:51
  • I don't really understand what this has to do with SQL, or rather, why you would use sql for this task – Strawberry Jun 16 '19 at 20:34
  • [How to convert number of week into date?](https://stackoverflow.com/q/7078730). Also, you obviously don't have to do this 52-53 times. Just do it for the 0th/1st week (maybe depending on your iso), then add 7 days for each week to it. – Solarflare Jun 16 '19 at 21:11

0 Answers0