Paul Briërley

Reports instead of travel tables

31^st January 2023

In a recent discussion on the JISC timetable mailing list, I proposed the use of reports instead of travel tables as a way to optimise the schedule without the system performance hit which travel tables normally introduce. I had a few emails asking for details of how to do this, so I thought I'd write the process up here for reference.

The aim here is to find pairs of activities where:

• The same person attends each activity;
• Both activities run on the same date;
• Both activities are scheduled in different buildings;
• The finish time of one activity is the start time of the other;

The process is then broken into three chunks:

1. Getting a list of activities and their start times;
2. Getting a list of activities and their finish times;
3. Combining those lists to find any problem activities.

Part 1 - Activities and their start times

Our aim is to extract some data in the following format:

The important thing here is that you need to expand your data by students and occurrences (so an activity with 10 students attached which runs for 10 weeks will appear 100 times). It's a big dataset, but a relatively simple one.

Syllabus+ example

The following SQL will get the data from a Syllabus+ reporting database. Note that I'm assuming activity name and student set hostkeys are unique, and that buildings are represented by zones.

SELECT
 rdowner.V_STUDENTSET.Hostkey AS PersonIdentifier,
 rdowner.V_ACTIVITY.Name AS ActivityIdentifier,
 rdowner.V_ACTIVITY_DATETIME.StartDateTime AS StartDateAndTime,
 rdowner.V_ZONE.Name AS BuildingIdentifier
FROM
 rdowner.V_STUDENTSET
INNER JOIN
 rdowner.V_ACTIVITY_STUDENTSET ON
  rdowner.V_STUDENTSET.Id = rdowner.V_ACTIVITY_STUDENTSET.StudentSetId
INNER JOIN
 rdowner.V_ACTIVITY ON
  rdowner.V_ACTIVITY_STUDENTSET.ActivityId = rdowner.V_ACTIVITY.Id
INNER JOIN
 rdowner.V_ACTIVITY_DATETIME ON
  rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_DATETIME.ActivityId
INNER JOIN
 rdowner.V_ACTIVITY_LOCATION ON
  rdowner.V_ACTIVITY_LOCATION.ActivityId = rdowner.V_ACTIVITY.Id
INNER JOIN
 rdowner.V_LOCATION ON
  rdowner.V_LOCATION.Id = rdowner.V_ACTIVITY_LOCATION.LocationId
INNER JOIN
 rdowner.V_ZONE ON
  rdowner.V_ZONE.Id = rdowner.V_LOCATION.ZoneId;


Part 2 - Activities and their Finish Times

This stage is almost exactly the same as the previous example, except that we pull out the activity finish time instead of their start time.

Syllabus+ example

 rdowner.V_ACTIVITY_DATETIME.StartDateTime AS StartDateAndTime,

 rdowner.V_ACTIVITY_DATETIME.EndDateTime AS EndDateAndTime,


Pulling it all together

With those two sets of data we now combine them in a way which lets us find problematic pairings. Here's the format we're going for:

When pulling these two sets of results together you need to match rows where the student IDs are the same, the start date time of one activity is the same as the end date time of the other and where the buildings are different. This will give you all the instances where a student has to move from one building to another for consecutive classes. You can then filter by problematic pairs of buildings to find your travel issues.

Syllabus+ Example

Assuming we call our two tables StartTimesTable and EndTimesTable, here's the SQL to combine them into the required format.

SELECT
 StartTimesTable.PersonIdentifier AS StudentID,
 EndTimesTable.ActivityIdentifier AS FirstActivity,
 EndTimesTable.EndDateTime AS FirstActivityEnd,
 EndTimesTable.BuildingIdentifier AS FirstActivityBuilding,
 StartTimesTable.ActivityIdentifier AS SecondActivity,
 StartTimesTable.StartDateAndTime As SecondActivityStart,
 StartTimesTable.BuildingIdentifier AS SecondActivityBuilding
FROM
 StartTimesTable
INNER JOIN
 EndTimesTable ON
  StartTimesTable.PersonIdentifier = EndTimesTable.PersonIdentifier,
  StartTimesTable.StartDateAndTime = EndTimesTable.EndDateAndTime,
  StartTimesTable.BuildingIdentifier <> EndTimesTable.BuildingIdentifier;