Generate Academic Calendar using MySQL

What?
We have a specific timetabling system for academic institutions and all our staff/students follow academic week numbers as opposed to calendar week numbers.

Why?
The aim of this article is to quickly generate a calendar for a full academic year for referencing by staff/students.

How?
Let us assume that we have a system holding a calendar in the following table (called "joes_weekstructure"):
copyraw
ID        AcademicSet       WeekNumber      StartDate 
--------- ----------------- --------------- ------------------
417       2012/2013         1               2012-07-16
418       2012/2013         2               2012-07-23
419       2012/2013         3               2012-07-30
...
467       2012/2013         51              2013-07-01
468       2012/2013         52              2013-07-08
  1.  ID        AcademicSet       WeekNumber      StartDate 
  2.  --------- ----------------- --------------- ------------------ 
  3.  417       2012/2013         1               2012-07-16 
  4.  418       2012/2013         2               2012-07-23 
  5.  419       2012/2013         3               2012-07-30 
  6.  ... 
  7.  467       2012/2013         51              2013-07-01 
  8.  468       2012/2013         52              2013-07-08 

I'll use this table to create the calendar.

Query #1: Works from command-line but may generate "Empty Query 1065"
copyraw
SELECT
	calendar.WeekNumber AS AcademicWeek,
	DATE_FORMAT(calendar.StartDate, '\%e-\%b-\%y') AS Mon,
	DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY), '\%e-\%b-\%y') AS Tue,
	DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY), '\%e-\%b-\%y') AS Wed,
	DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY), '\%e-\%b-\%y') AS Thu,
	DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY), '\%e-\%b-\%y') AS Fri,
	WEEKOFYEAR(calendar.StartDate) AS CalendarWeek
FROM
	joes_weekstructure calendar
WHERE
	calendar.SetID=(
		SELECT acyear.SetID 
		FROM joes_weekstructure acyear 
		WHERE NOW() >= acyear.StartDate 
		ORDER BY acyear.StartDate DESC 
		LIMIT 0,1
	)
ORDER BY
	calendar.weekNumber
  1.  SELECT 
  2.      calendar.WeekNumber AS AcademicWeek, 
  3.      DATE_FORMAT(calendar.StartDate, '\%e-\%b-\%y') AS Mon, 
  4.      DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY), '\%e-\%b-\%y') AS Tue, 
  5.      DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY), '\%e-\%b-\%y') AS Wed, 
  6.      DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY), '\%e-\%b-\%y') AS Thu, 
  7.      DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY), '\%e-\%b-\%y') AS Fri, 
  8.      WEEKOFYEAR(calendar.StartDate) AS CalendarWeek 
  9.  FROM 
  10.      joes_weekstructure calendar 
  11.  WHERE 
  12.      calendar.SetID=( 
  13.          SELECT acyear.SetID 
  14.          FROM joes_weekstructure acyear 
  15.          WHERE NOW() >= acyear.StartDate 
  16.          ORDER BY acyear.StartDate DESC 
  17.          LIMIT 0,1 
  18.      ) 
  19.  ORDER BY 
  20.      calendar.weekNumber 

Same Query but without using % (avoids MySQL Error 1065: Works within PHP scripts)
copyraw
SELECT 
      calendar.WeekNumber AS AcademicWeek, 
      CONCAT( 
           DAYOFMONTH(calendar.StartDate),  
           '-',  
           SUBSTR(MONTHNAME(calendar.StartDate), 1, 3),  
           '-',  
           YEAR(calendar.StartDate) 
      ) AS Mon, 
      CONCAT( 
           DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)),  
           '-',  
           SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)), 1, 3),  
           '-',  
           YEAR(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)) 
      ) AS Tue, 
      CONCAT( 
           DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)),  
           '-',  
           SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)), 1, 3),  
           '-',  
           YEAR(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)) 
      ) AS Wed, 
      CONCAT( 
           DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)),  
           '-',  
           SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)), 1, 3),  
           '-',  
           YEAR(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)) 
      ) AS Thu, 
      CONCAT( 
           DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)),  
           '-',  
           SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)), 1, 3),  
           '-',  
           YEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) 
      ) AS Fri, 
      WEEKOFYEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) AS CalendarWeek 
 FROM 
      joes_weekstructure calendar 
 WHERE 
      calendar.SetID=( 
           SELECT acyear.SetID  
           FROM joes_weekstructure acyear  
           WHERE NOW() >= acyear.StartDate  
           ORDER BY acyear.StartDate DESC  
           LIMIT 0,1 
      ) 
 ORDER BY 
      calendar.weekNumber
  1.  SELECT 
  2.        calendar.WeekNumber AS AcademicWeek, 
  3.        CONCAT( 
  4.             DAYOFMONTH(calendar.StartDate), 
  5.             '-', 
  6.             SUBSTR(MONTHNAME(calendar.StartDate), 1, 3), 
  7.             '-', 
  8.             YEAR(calendar.StartDate) 
  9.        ) AS Mon, 
  10.        CONCAT( 
  11.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)), 
  12.             '-', 
  13.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)), 1, 3), 
  14.             '-', 
  15.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)) 
  16.        ) AS Tue, 
  17.        CONCAT( 
  18.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)), 
  19.             '-', 
  20.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)), 1, 3), 
  21.             '-', 
  22.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)) 
  23.        ) AS Wed, 
  24.        CONCAT( 
  25.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)), 
  26.             '-', 
  27.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)), 1, 3), 
  28.             '-', 
  29.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)) 
  30.        ) AS Thu, 
  31.        CONCAT( 
  32.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)), 
  33.             '-', 
  34.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)), 1, 3), 
  35.             '-', 
  36.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) 
  37.        ) AS Fri, 
  38.        WEEKOFYEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) AS CalendarWeek 
  39.   FROM 
  40.        joes_weekstructure calendar 
  41.   WHERE 
  42.        calendar.SetID=( 
  43.             SELECT acyear.SetID 
  44.             FROM joes_weekstructure acyear 
  45.             WHERE NOW() >= acyear.StartDate 
  46.             ORDER BY acyear.StartDate DESC 
  47.             LIMIT 0,1 
  48.        ) 
  49.   ORDER BY 
  50.        calendar.weekNumber 
For those of you who are familiar with this, I am simply using an alternative to the DATE_FORMAT() function in mySQL.

Should yield:
copyraw
AcademicWeek   Mon         Tue         Wed         Thu         Fri         CalendarWeek
-------------- ----------- ----------- ----------- ----------- ----------- ---------------
1              16-Jul-2012 17-Jul-2012 18-Jul-2012 19-Jul-2012 20-Jul-2012 29
2              23-Jul-2012 24-Jul-2012 25-Jul-2012 26-Jul-2012 27-Jul-2012 30
3              30-Jul-2012 31-Jul-2012 1-Aug-2012  2-Aug-2012  3-Aug-2012  31
...
51             1-Jul-2013  2-Jul-2013  3-Jul-2013  4-Jul-2013  5-Jul-2015  27
52	       8-Jul-2013  9-Jul-2013  10-Jul-2013 11-Jul-2013 12-Jul-2015 28
  1.  AcademicWeek   Mon         Tue         Wed         Thu         Fri         CalendarWeek 
  2.  -------------- ----------- ----------- ----------- ----------- ----------- --------------- 
  3.  1              16-Jul-2012 17-Jul-2012 18-Jul-2012 19-Jul-2012 20-Jul-2012 29 
  4.  2              23-Jul-2012 24-Jul-2012 25-Jul-2012 26-Jul-2012 27-Jul-2012 30 
  5.  3              30-Jul-2012 31-Jul-2012 1-Aug-2012  2-Aug-2012  3-Aug-2012  31 
  6.  ... 
  7.  51             1-Jul-2013  2-Jul-2013  3-Jul-2013  4-Jul-2013  5-Jul-2015  27 
  8.  52           8-Jul-2013  9-Jul-2013  10-Jul-2013 11-Jul-2013 12-Jul-2015 28 


This query in T-SQL (SQL Server 2008 R2) for CMIS Facility
copyraw
SELECT			
	calendar.WeekNumber AS AcademicWeek,		
	CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), 1, 3) + '-' + CAST(DATEPART(yyyy, calendar.StartDate) AS VARCHAR(4)) AS Monday,		
	CAST(DATEPART(dd, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 1, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(4)) AS Tuesday,		
	CAST(DATEPART(dd, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 2, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(4)) AS Wednesday,		
	CAST(DATEPART(dd, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 3, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(4)) AS Thursday,		
	CAST(DATEPART(dd, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 4, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(4)) AS Friday,		
	CAST(DATEPART(dd, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 5, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(4)) AS Saturday,		
	CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4)) AS Sunday,		
	DATEPART(wk, CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4))) AS CalendarWeek		
FROM			
	cmisuser.WEEKSTRUCTURE calendar	
/* specific to our calendars 	
WHERE			
	calendar.SetID=(		
		SELECT	TOP 1 acyear.SetID 
		FROM	cmisuser.WEEKSTRUCTURE acyear 
		WHERE	acyear.StartDate >= GETDATE()
		ORDER BY acyear.StartDate DESC)	
*/
ORDER BY			
	calendar.weekNumber
  1.  SELECT 
  2.      calendar.WeekNumber AS AcademicWeek, 
  3.      CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), 1, 3) + '-' + CAST(DATEPART(yyyy, calendar.StartDate) AS VARCHAR(4)) AS Monday, 
  4.      CAST(DATEPART(dd, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 1, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(4)) AS Tuesday, 
  5.      CAST(DATEPART(dd, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 2, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(4)) AS Wednesday, 
  6.      CAST(DATEPART(dd, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 3, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(4)) AS Thursday, 
  7.      CAST(DATEPART(dd, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 4, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(4)) AS Friday, 
  8.      CAST(DATEPART(dd, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 5, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(4)) AS Saturday, 
  9.      CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4)) AS Sunday, 
  10.      DATEPART(wk, CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4))) AS CalendarWeek 
  11.  FROM 
  12.      cmisuser.WEEKSTRUCTURE calendar 
  13.  /* specific to our calendars 
  14.  WHERE 
  15.      calendar.SetID=( 
  16.          SELECT    TOP 1 acyear.SetID 
  17.          FROM    cmisuser.WEEKSTRUCTURE acyear 
  18.          WHERE    acyear.StartDate >= GETDATE() 
  19.          ORDER BY acyear.StartDate DESC) 
  20.  */ 
  21.  ORDER BY 
  22.      calendar.weekNumber 
Category: MySQL :: Article: 453

© 2024 Joel Lipman .com. All Rights Reserved.