CODEWITHSUNDEEP
phpmysqlsql
Andrew Duvall
Andrew Duvall

Reputation: 3

mysql time slot query with a start time and end time and over running times

I have a mysql query that returns time slots. I have a table called timeslots that contains starttimeb, endtimeb and employeeb. Booked Starttime and endtime fields pull from a different table called schedule. The problem is I can only assign appointments defined by the timeslots table.

For example I would like slots 8:00 to 10:00, 10:00 to 12:00, 12:00 to 14:00, 14:00 to 16:00 and 16:00 to 18:00 not available if there is 1 scheduled item with a starttime of 08:00 and a endtime of 18:00 in the schedule table. Business hours are defined as 8:00 to 18:00 so only two hour time blocks are available during that time and it is possible an appointment may be booked for more than the 2 hour time slots provided in the timeslots table, so for example if 8:00 to 12:00 has been booked I would only want 12:00 to 14:00, 14:00 to 16:00 and 16:00 to 18:00 to be available. Or if 9:00 to 11:00 was booked only 12:00 to 14:00, 14:00 to 16:00 and 16:00 to 18:00 would be available. 

mysql_query( "SELECT *
FROM timeslots
LEFT OUTER JOIN
 (SELECT 
   starttime
 , endtime
 , custno
 , id
FROM schedule
WHERE schedule.date = '2012-12-14'
 ) AS a
 ON a.starttime >= timeslots.starttimeb
 AND a.endtime <= timeslots.endtimeb
 AND a.custno = timeslots.custnob" );

The query above returns something like this... On my app it is set that if starttime is null timeslot is available.

starttimeb endtimeb custnob            starttime endtime  custno          id
08:00:00   10:00:00 Michael W Sloan    08:00:00  10:00:00 Michael W Sloan 15670
10:00:00   12:00:00 Michael W Sloan    NULL      NULL     NULL            NULL
12:00:00   14:00:00 Michael W Sloan    NULL      NULL     NULL            NULL 
14:00:00   16:00:00 Michael W Sloan    14:00:00  16:00:00 Michael W Sloan 15673
16:00:00   18:00:00 Michael W Sloan    NULL      NULL     NULL            NULL
08:00:00   10:00:00 James T Reesor     08:00:00  10:00:00 James T Reesor 15664
10:00:00   12:00:00 James T Reesor     NULL      NULL     NULL            NULL
12:00:00   14:00:00 James T Reesor     NULL      NULL     NULL            NULL
14:00:00   16:00:00 James T Reesor     NULL      NULL     NULL            NULL
16:00:00   18:00:00 James T Reesor     16:00:00  18:00:00 James T Reesor 15674
08:00:00   10:00:00 Carlton L. Pierce  NULL      NULL     NULL            NULL
10:00:00   12:00:00 Carlton L. Pierce  NULL      NULL     NULL            NULL
12:00:00   14:00:00 Carlton L. Pierce  12:00:00  14:00:00 Carlton L. Pierce 15671
14:00:00   16:00:00 Carlton L. Pierce  NULL      NULL     NULL            NULL
16:00:00   18:00:00 Carlton L. Pierce  NULL      NULL     NULL            NULL
08:00:00   10:00:00 Frankie D Aldridge NULL      NULL     NULL            NULL
10:00:00   12:00:00 Frankie D Aldridge NULL      NULL     NULL            NULL
12:00:00   14:00:00 Frankie D Aldridge NULL      NULL     NULL            NULL
14:00:00   16:00:00 Frankie D Aldridge NULL      NULL     NULL            NULL
16:00:00   18:00:00 Frankie D Aldridge NULL      NULL     NULL            NULL
08:00:00   12:00:00 Michael W Sloan    08:00:00  10:00:00 Michael W Sloan 15670

Upvotes: 0

Views: 1656

Answers (1)

Terje D.
Terje D.

Reputation: 6315

It is difficult to understand what the question is, but if the problem is that you want to find if any rows from schedule overlaps with the times in the timeslot table, changing

ON a.starttime >= timeslots.starttimeb
AND a.endtime <= timeslots.endtimeb

which only matches schedule rows that both starts and ends within a given timeslot, into

ON a.starttime < timeslots.endtimeb
AND a.endtime > timeslot.starttimeb

which matches schedule rows that is neither completely before nor completely after the timeslot in question, might help.

Upvotes: 1

Related Questions