Inserting incremental weeks in MySQL

Situation:
I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are academic week numbers. So I can't use the built-in functions.

The current structure looks similar to this:

copyraw
ID        SetID            WeekNumber             StartDate
--------- ---------------- ---------------------- ----------------------------
1         2011/2012        1                      2011-07-18 00:00:00
...
52        2011/2012        52                     2012-07-09 00:00:00
  1.  ID        SetID            WeekNumber             StartDate 
  2.  --------- ---------------- ---------------------- ---------------------------- 
  3.  1         2011/2012        1                      2011-07-18 00:00:00 
  4.  ... 
  5.  52        2011/2012        52                     2012-07-09 00:00:00 

Joe you're an idiot!
You might say to me why not run the CMIS Facility application and add a new set, it will put these dates in automatically.

Herein lies the problem
The reason I'm doing this is for another system which decided to "cleverly" use the exports from CMIS Facility so that all the weeks correspond to the rest of the academic data. Unfortunately the developer wrote a system he felt would last the rest of his PhD degree, it's a shame he started in his last year. He used functions to mktime and simulate the dates. A function goes in with a normal calendar date and returns an academic week number and the week commencing date.

Problem?
The 1st of January 2010 was a Friday. The 1st of January 2011 was a Saturday. The developer felt that as long as you adjust the script each year you could make the system last another year. Shame he also forgot the academic year ends halfway in a normal people's calendar, so you actually have to adjust this twice a year.

Complicated
Facility works like this, ask it what date it is on Tuesday in Week 52 of the Academic year 2011/2012... It tells you 2012-07-10. Ask it what date it is on 2012-07-10 and it doesn't have a clue.

Anyway, enough faffing!
Here's a script I used to generate the SELECT query. If you run it, it should return the additional 52 rows we want add. Note that the table "WeekStructure" could have been any table with more than 52 rows, I'd say specify the table you will NOT be adding these rows to...

copyraw
SET @count:=0;
SELECT 
	'2012/2013' AS SetID, 
	(@count:=@count+1) AS WeekNumber, 
	@thisDate:=DATE_ADD("2012-07-09 00:00:00", INTERVAL (@count*1) WEEK) as StartDate 
FROM
	WeekStructure
WHERE 
	@count
  1.  SET @count:=0
  2.  SELECT 
  3.      '2012/2013' AS SetID, 
  4.      (@count:=@count+1) AS WeekNumber, 
  5.      @thisDate:=DATE_ADD("2012-07-09 00:00:00", INTERVAL (@count*1) WEEK) as StartDate 
  6.  FROM 
  7.      WeekStructure 
  8.  WHERE 
  9.      @count 

Add it to an existing table
Yes well now use that with an INSERT statement. Note how we're inserting into the correct table (WeekStructure) but retrieving data from a random table which has at least 52 rows (eg. 'AnotherTableWith52Rows') so that the loop keeps going. The other table is because I'm not familiar with Temporary tables in MySQL (though I imagine it's a lot easier than t-sql) but this is a query using what I do know in MySQL.

@count is the week number counter
@lastdate is the last week commencing date in the table

copyraw
SET @count:=0;
SET @lastDate:="2012-07-09 00:00:00";
INSERT INTO WeekStructure (SetID, WeekNumber, StartDate)
SELECT 
	'2012/2013' AS SetID, 
	(@count:=@count+1) AS WeekNumber, 
	@thisDate:=DATE_ADD(@lastDate, INTERVAL (@count*1) WEEK) as StartDate 
FROM
	AnotherTableWith52Rows
WHERE 
	@count
  1.  SET @count:=0
  2.  SET @lastDate:="2012-07-09 00:00:00"
  3.  INSERT INTO WeekStructure (SetID, WeekNumber, StartDate) 
  4.  SELECT 
  5.      '2012/2013' AS SetID, 
  6.      (@count:=@count+1) AS WeekNumber, 
  7.      @thisDate:=DATE_ADD(@lastDate, INTERVAL (@count*1) WEEK) as StartDate 
  8.  FROM 
  9.      AnotherTableWith52Rows 
  10.  WHERE 
  11.      @count 
Now I needed to populate the previous years:
copyraw
SET @count:=53;
SET @firstDate:="2008-07-21 00:00:00";
-- INSERT INTO joes_weekstructure (SetID, WeekNumber, StartDate)
SELECT 
	'2007/2008' AS SetID, 
	(53 - (@count:=@count-1)) AS WeekNumber,
	@thisDate:=DATE_ADD(@firstDate, INTERVAL -(@count*1) WEEK) as StartDate 
FROM
	Staff
WHERE 
	@count>1;
  1.  SET @count:=53
  2.  SET @firstDate:="2008-07-21 00:00:00"
  3.  -- INSERT INTO joes_weekstructure (SetID, WeekNumber, StartDate) 
  4.  SELECT 
  5.      '2007/2008' AS SetID, 
  6.      (53 - (@count:=@count-1)) AS WeekNumber, 
  7.      @thisDate:=DATE_ADD(@firstDate, INTERVAL -(@count*1) WEEK) as StartDate 
  8.  FROM 
  9.      Staff 
  10.  WHERE 
  11.      @count>1
Category: MySQL :: Article: 342

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.