Here's a good logic challenge:
I have to come up with figures for room occupancy based on timetables data.
For most rooms this is easy. No two events can be timetabled at once so you just add up the all booking durations and subtract that from the available hours.
It gets tricky when it comes to the rooms where we do allow concurrent bookings. Unfortunately it's extra important these are included in the report because they are usually large, expensive teaching workshops where optimal usage is particularly relevant.
I am sure code for this has been written many times before.
As far as I can see what I am essentially trying to do is just find which blocks stretches of time have no activity in them. The most distilled procedural description of this is as follows:
For each day in each room in each week sort all bookings by start time.
Step through checking on each row how much time elapsed between the start time and the latest end time so far
It's that "so far" bit that has lost me the hope of doing this without a cursor. If bookings were of uniform length I could rely on the start time sort effectively sorting the end times and therefore I could always just compare to the previous row - something which can be done set-based.
The fact is that the the big room booking which reaches way out until the end of the day
may also have been the very first
booking of the day so comparing Booking Four
starting at midday with the previous Booking Three
which ended at 11:30 will count half an hour unused time which isn't the case.
The only alternative to this cursor is a very overcomplicated set of operations comparing All with All, to batch together clusters of bookings then identify the outer limits of these clusters. Finding the overlaps is easy but whittling down and grouping and categorising the types of overlaps, using different approaches for instance if 3 events are concurrent to when 3 events are in a cluster but one overlaps with the other 2 but those 2 don't overlap with each other.
However I might have missed a trick and be wrong about the necessary unwieldiness of the above. If there's some good catch-all set-based logic somebody has used for something similar your help would be greatly appreciated. I've not used cursors before and I don't want to slog through learning it just to find going through a 69000 row temp table (that's just for the rooms with concurrent booking) is just too stupidly slow even for an overnight procedure.