Skip to main content
Salesforce

Return to Question

added 143 characters in body; edited title
Source Link
Robs
Robs
  • 9.4k
  • 21
  • 119
  • 224

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

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 BookedAvailable
09 Keith 13:00 13:30 Available
10 Keith 13:30 14:00 Available

I want to return thethese 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?

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

I'm storing peoples availability in slots of 30 minutes, I would like to select 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 Booked
09 Keith 13:00 13:30 Available
10 Keith 13:30 14:00 Available

I want to return the 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 not the other rows because they would not total 90 minutes of consecutive time.

How should I approach this SOQL query?

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

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?

added 54 characters in body
Source Link
Robs
Robs
  • 9.4k
  • 21
  • 119
  • 224

I'm storing peoples availability in slots of 30 minutes, I would like to select 3 consecutiveconsecutive slots of availability which are availableavailable, 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 Booked
09 Keith 13:00 13:30 Available
10 Keith 13:30 14:00 Available

I want to return the 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 not the other rows because they would not total 90 minutes of consecutive time.

How should I approach this SOQL query?

I'm storing peoples availability in slots of 30 minutes, I would like to select 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)

How should I approach this SOQL query?

I'm storing peoples availability in slots of 30 minutes, I would like to select 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 Booked
09 Keith 13:00 13:30 Available
10 Keith 13:30 14:00 Available

I want to return the 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 not the other rows because they would not total 90 minutes of consecutive time.

How should I approach this SOQL query?

added 54 characters in body
Source Link
Robs
Robs
  • 9.4k
  • 21
  • 119
  • 224

I'm storing peoples availability in slots of 30 minutes, I would like to select 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)

How should I approach this SOQL query?

I'm storing peoples availability in slots of 30 minutes, I would like to select 3 consecutive slots of availability, 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

How should I approach this SOQL query?

I'm storing peoples availability in slots of 30 minutes, I would like to select 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)

How should I approach this SOQL query?

Source Link
Robs
Robs
  • 9.4k
  • 21
  • 119
  • 224