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:
ID SetID WeekNumber StartDate --------- ---------------- ---------------------- ---------------------------- 1 2011/2012 1 2011-07-18 00:00:00 ... 52 2011/2012 52 2012-07-09 00:00:00
- ID SetID WeekNumber StartDate
- --------- ---------------- ---------------------- ----------------------------
- 1 2011/2012 1 2011-07-18 00:00:00
- ...
- 52 2011/2012 52 2012-07-09 00:00:00
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...
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
- 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
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
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
- 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
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;
- 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;