SQL Challenge – Planning a Conference Schedule

Coming Wednesday, we organize the fourth AMIS Academy (Wednesday 27th June – free mini-conference at our place), a mini-conference with 16 presentations in four consecutive timeslots. One of our challenges in organizing this conference is planning the presentations. Ideally, every one can visit as many of their preferred sessions as possible – by not scheduling presentations that appeal to the same audience in the same time slot.

I have just arrived home after attending the ODTUG conference. While it was a good week and I enjoyed myself, I did have a few points of criticism with the organizers: it occurred frequently that in one time slot there were several presentations that I wanted to attend while in other slots there was no presentation that appealed to me. Clearly a planning flaw, at least in my opinion.

So for next Wednesday I want to try to cater for as many preferences as I can. In order to at least know about the preferences of the audience, I have conducted a survey on our intranet; here my colleagues could indicate for each of the 16 presentations whether they really wanted to attend it (3), did not want to attend it (1), were neutral (3) or consider it an option (4) – depending on the other options in a time slot. Finally they could indicate which presentations they had to attend because they were one of the presenters themselves (5).

This survey was processed into the following data model:

The challenge now is to find out which presentation can be planned in the same time slot with the smallest number of visitors disappointed.
So I try to build a query that gives me an overview for each session of all other sessions and the number of people that want to visit both of them. The smaller that number, the better the two are suited to planning in the same time slot. So for example if I find out that for the presentation " Could You Pass That Data Please? On ADF Applications Leveraging BPEL and ESB Powered Data Services" (by Peter Ebell) there is just one visitor that also wants to visit "AMIS Werk In Uitvoering" and two that also want to visit "Report from JavaOne 2007" and the "Oracle SQL and PL/SQL Quiz" while there are 16 visitors that also want to attend the presentations on APEX 3.0 and UML Use Cases, it is easy to decide to plan those first three in the same time slot and the APEX 3.0 and UML Use Cases presentations in a different time slot.

The query needs to select all sessions, then for each session find the visitors that scored it with 3 (want to visit) or 5 (have to be there as one of the presenters) and then for those visitors find all the sessions they want to (3) or have to (5) attend. Counting per other session the number of visitors for a session we find out which other session has the smallest overlap and which one the largest. The ones with the smallest overlap can be scheduled together.

In this example, we find that session 1 can best be schedules with session 4 and perhaps session 2 as it has the smallest number of visitors disappointed. Only 1 person who is interested in visiting session 1 also wants to visit session 4 – that is John. Scheduling session 1 in the same slot as session 3 or 5 would disappoint 5 people, clearly not what we want.

The query could look something like this:

select ssn.title "session"
,      ssn2.title other_session
,      count( case nvl(pfe2.otn_id,0)
              when 3 then 1
              when 5 then 1
              else null
              end
             ) overlaps
from   sessions ssn
       inner join
       preferences pfe
       on (pfe.ssn_id = ssn.id)
       inner join
       preferences pfe2
       on (pfe2.vsr_id = pfe.vsr_id)
       right outer join
       sessions ssn2
       on (pfe2.ssn_id = ssn2.id)
where  pfe.otn_id in (3,5)
and    ssn.id != ssn2.id
group
by     ssn.title
,      ssn2.title
order
by     1, 3
/
 

it first selects all sessions, joining them with the visitors that have a strong preference (3) or necessity (5) of visiting them. For these visitors, we right outer join there preferences for other sessions to all other sessions. For all combinations of these visitors and there preferences for other sessions, we only count preferences with option 3 or 5 (those that express a strong interest in visiting another session). The query returns for every session a list of all other sessions and the number of people interested in visiting both of them, order by that number.

From the query result for example for "ORACLE SQL AND PL/SQL QUIZ: SMOKIN WATER"

ORACLE SQL AND PL/SQL QUIZ: SMOKIN WATER:
=========================================
KC BC & A: Use Cases - een introductie                             1
AMIS Werk In Uitvoering                                            1
Demo Mercury: Testplannen aan de hand van Business Components      1
KC Architectuur - Workshop karakteristieken AMIS Softwarestudio    2
KC Project Management: Testen en getest worden gestandaardiseerde  2
Report from ODTUG 2007 Kaleidoscope - What's Hot & What's Not in   2
De rol van een Technical Infrastructure Consultant - case study    2
Readers Digest: Een wereldwijde Oracle EBS Implementatie           2
Apex 3.0; wat is het en wat kan je er eigenlijk mee?               3
JavaOne 2007 - Wat wordt het helemaal in Java, SOA en Web 2.0?     3
Could You Pass That Data Please? ADF Applications Leveraging BPEL  4
KC Server Development: CodeGen: Genereer Standaard, Concentreer    4
Een goed CV of: hoe houd ik mijn opdracht leuk                     5
Never Mind The BuzzHypes (part 2)                                  6
 

we can tell that we have the least overlap in terms of audience interest with the presentations on Use Cases, AMIS Werk In Uitvoering and Demo Mercury. The largest number of disappointed people comes with planning the quiz at the same time as "Never mind the buzzhypes". 41 of the visitors for next Wednesday have participated in the survey.

While this is far from an automated planning tool – it provides a lot of insight in the presentation that can go together in single time slot.