How to get a list of available time slots from calendar database

Question

I have a event/calendar MySQL table where each user have multiple appointments/events throughout the day. If one user can't make that appointment/event "because he/she are running behind on other appointment" I need to be able to re-assign this appointment to a different available user. So I need to display a suggestion of the top 5 users that are available for the scheduled time frame and can take this appointment, a manager will be able to re-assign this appointment to one of the suggested users.

My events table looks something like this

CREATE TABLE `calendar_events` (
   `event_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `start_on` datetime NOT NULL,
   `end_on` datetime NOT NULL,
   `subject` varchar(255) NOT NULL,
   `event_type` enum('Phone Call','Meeting','Event','Appointment','Other') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'Phone Call',
   `all_day_event` tinyint(1) DEFAULT '0' COMMENT '1 = all day event, 0 = no',
   `phone_call_id` int(11) unsigned DEFAULT NULL,
   `account_id` int(11) unsigned DEFAULT NULL,
   `client_id` int(11) unsigned DEFAULT NULL,
   `owner_id` int(11) unsigned NOT NULL,
   `created_by` int(11) unsigned NOT NULL,
   `created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `modified_by` int(11) unsigned DEFAULT NULL,
   `modified_on` datetime DEFAULT NULL,
   `event_location` varchar(255) DEFAULT NULL,
   `event_notes` varchar(10000) DEFAULT NULL,
   `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0 = purged, 1 = active, 2=pass, 3 = cancled, 5 = waiting for auditor to be enabled',
   PRIMARY KEY (`event_id`),
   UNIQUE KEY `phone_call_id` (`phone_call_id`,`account_id`,`client_id`),
   KEY `client_id` (`client_id`),
   KEY `account_id` (`account_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

so lets for event_id = 100 is assigned to user_id = 2 and scheduled to start_on = '2014-09-21 10:00:00' and end_on '2014-09-21 10:00:00'

and user_id = 5 has appointment start_on '2014-09-21 11:45:00' and end_on '2014-09-21 12:30:00'

and user_id = 2 can not make his appointment that is scheduled for '2014-09-21 10:00:00' so they system will suggest user_id = 5 as he will be for the following 105 minutes.

The the final data set will need to be

event_id org_owner  suggested_owner   available_for
100      2          5                 105

The following query will give me a list of all available users to from the users table along with a start_on end_on value if the user have an event scheduled (one user can have multiple records.) If the start_on is null in this query that means this user does not have any event otherwise it will return the start of each event.

So if user ID appears in the query above and have a NULL value in the start_on column, this means that this user is available all day so this user should be 1 of the 5 users to recommend because it has one of the highest availability. But if a user has one/multiple rows in the data set with a non-null value in the start on then, we need to look at the start_on that is the closest to the event and then recommend the top 5 that have the greatest availability value.

SELECT user_id, start_on, end_on, subject
FROM view_users AS su
LEFT JOIN calendar_events AS c ON c.owner_id = su.user_id AND c.start_on NOT BETWEEN '2014-09-30 00:00:00' AND '2014-09-30 23:59:59' AND c.status = 1
WHERE su.is_available_today = 1

How can I extract this data set?

Answer 1

First proposal edited thanks to your help, just need to take care of users that don't have any events (could be achieved with a left join in 't' subquery). This could be improved a lot, but right now I'm a bit tired :)

SELECT
c.event_id,                     -- Event id
c.owner_id AS org_owner,        -- Original owner of event
t.owner_id AS suggested_owner,  -- Suggested new user
c.start_on,                     -- Event start
t.free_from,                    -- Owner free slot start
t.free_to,                  -- Owner free slot end
TIME_TO_SEC( TIMEDIFF( t.free_to, c.start_on ) ) /60 AS available_for   -- Availibility of minutes (diff between event start and free slot end)

FROM calendar_events AS c

-- Join with free slots
LEFT JOIN (
    -- Add a slot for beginning, 1999-01-01 to first event start
    SELECT * FROM (
        SELECT owner_id, '1900-01-01' AS free_from, MIN( start_on ) AS free_to
        FROM calendar_events c3
        GROUP BY owner_id
    ) AS deb

    UNION

    -- select free slots by taking the event end and the following event start
    SELECT owner_id, `end_on` AS free_from, (
        SELECT start_on
        FROM calendar_events c2
        WHERE c2.owner_id = c1.owner_id
        AND c2.start_on > c1.end_on
        ORDER BY c2.start_on
        LIMIT 0 , 1
    ) AS free_to

    FROM calendar_events c1

    UNION 

    -- Add a slot for end, last event end to 2100-01-01
    SELECT * FROM (
        SELECT owner_id, MAX( end_on ) AS free_from, '2100-01-01' AS free_to
        FROM calendar_events c3
        GROUP BY owner_id
    ) AS end
) AS t ON t.owner_id <> c.owner_id
-- Join avoid using same user and ensure free slot matches event dates
AND t.free_from <= c.start_on AND t.free_to >= c.end_on
WHERE c.status = 1
AND c.event_id =52
GROUP BY t.owner_id     -- To avoid multiple free slots by user
ORDER BY available_for DESC -- Sort to list biggest slots first
LIMIT 0, 5              -- Only five first matching users

Good luck :)

Answer 2

How about this:

SELECT event_id, owner_id, start_on INTO @eventid, @user, @start_on 
FROM calender_events WHERE event_id = 100;

SELECT @event_id event_id,
    @user org_owner,
    c.owner_id suggested_owner,
    TIMESTAMPDIFF(MINUTE, $start_on, COALESCE(c.min_start, DATE(@start_on) + INTERVAL 18 HOUR)) available_for
FROM
    users u
LEFT JOIN
    (SELECT
          owner_id,
          MIN(start_on) 
     FROM
          calender_events
     WHERE
          (start_on BETWEEN @start_on AND DATE(@start_on) + INTERVAL 18 HOUR) 
          OR
          (start_on BETWEEN DATE(@start_on) AND DATE(@start_on) + INTERVAL 18 HOUR AND all_day_event = 1)
     GROUP BY owner_id
    ) c
    ON u.user_id = c.owner_id
WHERE u.user_id <> @user
ORDER BY available_for DESC
LIMIT 5

Maybe you have to adjust the INTERVAL, I just made an assumption the daay ending 6 P.M.

Answer 3

Try this:

SELECT
    co.event_id,
    co.owner_id org_owner,
    su.user_id suggested_owner,
    ifnull(min((to_seconds(c.start_on) - to_seconds(co)) / 60), 999) available
FROM calendar_events co
CROSS JOIN view_users su
LEFT JOIN calendar_events c ON c.owner_id = su.user_id
    AND c.start_on BETWEEN co.start_on AND date(adddate(co.start_on, 1))
    AND c.status = 1
WHERE co.event_id = 100
AND su.is_available_today = 1
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 5

Users that have no appointments for day day after the target event get assigned the available value of "999", putting them at the top of the list.

The next event for each user is found using min() over the time gap, and all users are sorted largest time gap first, them limit gives you the top 5.