Geek Answers Handbook

Algorithm for available times on the agenda

I am really stuck in this situation.

I have these two tables:

  • employee_working_schedule (saves employee start and end times on a specific date)
  • employee_appointments

Suppose we saved these lines

employee_working_schedule:

start | end 10:00 | 18:00

employee_appointments:

 start | end 10:10 | 11:00 11:20 | 12:00 14:30 | 15:20

In this case, I want to show that the available time:

 10:00 | 10:10 11:00 | 11:20 12:00 | 14:30 15:20 | 18:00

Is there any way to do this using SQL? I tried to achieve with php, but have not yet succeeded.

Any help would be appreciated.

+4
source share
3 answers

Here's how to do it in pure PHP:

 class TimeSpan { function __construct($start, $end) { $this->start = $start; $this->end = $end; } function starttime() { list($hour, $minute) = explode(":", $this->start); return (int)$hour * 60 + (int)$minute; } function endtime() { list($hour, $minute) = explode(":", $this->end); return (int)$hour * 60 + (int)$minute; } } function convert_to_time($minutes) { $hour = (int) ($minutes / 60); $minutes = $minutes % 60; return str_pad($hour, 2, '0', STR_PAD_LEFT) . ':' . str_pad($minutes, 2, '0', STR_PAD_LEFT); } function open_times($shift, $appointments) { $alltimes = array_fill_keys(range($shift->starttime(), $shift->endtime()), 1); foreach ($appointments as $appt) { $alltimes = array_diff_key($alltimes, array_fill_keys(range($appt->starttime() + 1, $appt->endtime() - 1), 1)); } $groups = array(); $active_group = 0; $output = array(); $output_counter = 0; $nums = array_keys($alltimes); foreach( $nums as $k => $num ) { if( $k !== 0 && $nums[$k] !== $nums[$k-1]+1 ) $active_group ++; $groups[ $active_group ][] = $num; } foreach( $groups as $group ) { $first = array_shift( array_values($group) ); $output[$output_counter][] = $first; $last = array_pop( array_values($group) ); if( $first !== $last ) $output[$output_counter][] = $last; $output_counter++; } foreach ($output as &$span) { $span[0] = convert_to_time($span[0]); $span[1] = convert_to_time($span[1]); } return $output; } $shift = new TimeSpan("10:00", "18:00"); $appointments = array( new TimeSpan("10:10", "11:00"), new TimeSpan("11:20", "12:00"), new TimeSpan("14:30", "15:20"), ); print_r(open_times($shift, $appointments));

OUTPUT

 Array ( [0] => Array ( [0] => 10:00 [1] => 10:10 ) [1] => Array ( [0] => 11:00 [1] => 11:20 ) [2] => Array ( [0] => 12:00 [1] => 14:30 ) [3] => Array ( [0] => 15:20 [1] => 18:00 ) )
+3
source

I can give you a solution in PHP, but it would certainly be faster if you could find something in SQL

 //given you selected $employee_working_schedule $dayStart = DateTime::createFromFormat('h:i',$employee_working_schedule['start']); $dayEnd = DateTime::createFromFormat('h:i',$employee_working_schedule['end']); //then assuming your query statement is named $timeQuery and use PDO $nextFreeStart = $dayStart; $newFreeEnd = null; $freeTime = array();//here we will store the free time intervals while($times = $timeQuery->fetch(PDO::FETCH_ASSOC)){ $nextFreeEnd = DateTime::createFromFormat('h:i',$times['start']); $freeTime[] = $nextFreeEnd->diff($nextFreeStart); $nextFreeStart = DateTime::createFromFormat('h:i',$times['end']); } $freeTime[] = $dayEnd->diff($nextFreeStart); //close the day
0
source

Strategy: collect all the starting points, find the corresponding endpoint for each, and then throw away the lines that do not match.

This idea is written in MySQL (untested):

 SELECT eas.s AS s_start, MIN(eas.e) AS e_end FROM (( SELECT end AS s, start AS e FROM employee_appointments ) UNION ( SELECT start AS s,end AS e FROM employee_working_schedule )) eas WHERE end > start -- or >= if you want zero-time slots as well GROUP BY eas.s HAVING NOT EXISTS ( -- if appointments are disjoint, this is likely redundant SELECT 0 FROM employee_appointments kill WHERE s_start > kill.end AND kill.start > e_end )
0
source

More articles:


All Articles