How to select multiple sets of consecutive data/rows based on time using SOQL?

Question

I'm storing peoples availability in slots of 30 minutes, I would like to select multiple sets of 3 consecutive slots of availability which are available, such that I can return 90 minutes of continuous time that is available.

Availability object fields:

 - Availability ID
 - Person Ref/Lookup 
 - Start Date/Time 
 - End Date/Time
 - Status (Available/Booked)

Example Data (missing the date to make it easy to read)

01 Keith 09:00 09:30 Available
02 Keith 09:30 10:00 Booked
03 Keith 10:00 10:30 Booked
04 Keith 10:30 11:00 Available
05 Keith 11:00 11:30 Available
06 Keith 11:30 12:00 Available
07 Keith 12:00 12:30 Booked
08 Keith 12:30 13:00 Available
09 Keith 13:00 13:30 Available
10 Keith 13:30 14:00 Available

I want to return these 3 consecutive slots:

04 Keith 10:30 11:00 Available
05 Keith 11:00 11:30 Available
06 Keith 11:30 12:00 Available

And

08 Keith 12:30 13:00 Available
09 Keith 13:00 13:30 Available
10 Keith 13:30 14:00 Available

And not the other rows because they would not total 90 minutes of consecutive time.

How should I approach this SOQL query?

Answer 1

There's naught for it but post processing here. Your query will have to pull down all those example records, then you can loop over them. Something like the following ought to work.

// cache your index variable outside the loop
// so you can reference it afterward
Integer i, consecutive = 0;

for (i = 0; i < records.size(); i++)
{
    if (records[i].Is_Available__c) consecutive++;
    else consecutive = 0;

    if (consecutive == 3) break;
}

if (consecutive < 3)
{
    // didn't have such a range
}
else
{
    Datetime endTime = records[i].End_Time__c;
    Datetime startTime = records[i-2].Start_Time__c;
}

If you want to return all of the times you get three available slots in a row, you would instead use a List<Integer> indices collection to store the index variable values for any iteration where consecutive >= 3.

Answer 2

I don't think there is any way to do this with just a SOQL query, but if you include some apex processing you can get the data you are looking for:

List<List<Availability__c>> groupedAvailability= new List<List<Availability__c>>();

List<Availability__c> availabilities = [select ID, Start_Date_Time__c, End_Date_Time__c 
                                       from Availability__c where Status__c = 'Available' 
                                       Order by Start_Date_Time__c];

for(integer i = 0, finalInd = availaibilities.size() - 2; i < finalInd; ++i)
{
    boolean badGroup = false;
    for(integer j = i + 1; !badGroup && j <= i + 2; ++j)
    {
        if(availabilities[j].Start_Date_Time__c != avalabilities[j - 1].End_Date_Time__c)
        {
            badGroup = true;
        }
    }
    if(!badGroup)
    {
        groupedAvailability.add(new List<Availability__c>{
            availabilities[i], availabilities[i + 1], availabilities[i + 2]});
    }
}

Answer 3

How about this Apex:

DateTime start = ...;
DateTime end = start.addMinutes(90);

Availability__c[] slots = [
        select ...
        from Availability__c
        where Start__c >= :start
        and End__c < :end
        order by Start__c
        ];