MySQL Forums
Forum List  »  Newbie

Open Slots SELECT help
Posted by: Don Wieland
Date: July 03, 2021 02:05PM

I have two tables:

CREATE TABLE `Availability` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`k__pk_av_id` int(11) DEFAULT NULL,
`k_fk_resource_id` int(11) DEFAULT NULL,
`av_ts_start` timestamp NULL DEFAULT NULL,
`av_ts_end` timestamp NULL DEFAULT NULL,
`av_appt_type_id` int(11) DEFAULT NULL,
`av_note` text COLLATE utf8_unicode_ci,
`wv_code` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `Appointments` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`k__pk_appt_id` int(11) DEFAULT NULL,
`k_fk_resource_id` text COLLATE utf8_unicode_ci,
`appt_ts_start` timestamp NULL DEFAULT NULL,
`appt_ts_end` timestamp NULL DEFAULT NULL,
`appt_appt_type_id` int(11) DEFAULT NULL,
`appt_note` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7449 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Availability (AVs) store when resources are available
Appointments (Appts) store existing appointments
You cannot create an appointment unless the entire appt is overlapped by an availability and there is no overlap between another appt.

I have a php form where the user can specify: one-to-many resources, one date, and a appt_duration (in minutes- 15,30,45,60, etc...).

I would like to feed those values to a SELECT QUERY that finds optimal OPEN slots for the user to request.

I am currently displaying a table GRID which has Times running vertically and Resources horizontally. I am shading the AVs is a blue background with appts appts shaded yellow with details. Currently you can select any AV cell to prompt a form to request a AV slot.

It is easy to just display all AVs and Appts on the grid, BUT my client is wanting something different. They want to display CELLS that will work for the appt_duration and these conditions:

If the resource has ZERO appts, display all the AV Cells blue. All is selectable.

If there are existing Appts display singles open AV cells that stack prior/after any existing appts for that date.

A use case scenario would be:

The users form has 30min appt_duration
ResA has an AV from 9am-6pm
ResA has 3 existing Appts (10am-10:45, 11:45am-12:45pm, 2:30pm-3:15pm)

Based on that data, the following cells would be shaded blue and selectable:

9:30am (30mins prior to 10am appt)
10:45am (first slot after 10am-10:45am appt)
11:15am (30mins prior to 11:45am appt)
12:45am (first slot after 11:15am-12:45pm appt)
2pm (30mins prior to 2:30pm appt)
3:15pm (first slot after 2:30pm-3:15pm appt)

This algorithm is to minimize appt GAPS for resources.

It is possible to do this in a single SELECT query or would I have to do several queries and do some backflips with PHP post query.

Any insight on this would be apreciated.

Don

Options: ReplyQuote


Subject
Written By
Posted
Open Slots SELECT help
July 03, 2021 02:05PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.