I want to run a select query that looks for gaps between the value for ends_at on one row and the value for starts_at on the next row. After days of trawling forums and web sites here's what I've come up with:
SELECT *, TIMEDIFF(b.starts_at, a.ends_at) AS 'length'
FROM bookings AS a
JOIN bookings AS b ON a.emp_id = b.emp_id
AND a.ends_at < b.ends_at
GROUP BY a.ends_at
don't ask why I'm using that query - I'm in way over my head and that's the sad result of a lot of trial and error. Ideally, I'd like to set conditions specifying the earliest start time and required appointment duration, but right now I'd settle just for a full list of available appointments.
I'm not even sure if this is possible in mysql. I've seen solutions for other DBs but they go on about pivot table and analytic functions and all sorts!
The chances of you finding a perfect solution to any problem on the internet is quite low - you'll generally just find good pointers. I'd just try to improve the SQL you have (or start afresh) and go through the following iterations:
Select out all the gaps for an employee
then show the length of each gap
then order by start time
then limit results to just one row
might also want to limit when the gaps can be ie 9 to 5 and not lunchtimes or weekends
also think about how to show 1st appointment next week
You might want to ask your teacher why he has a primary key on the id field - what benefit is it actually offering?
SELECT start_of_gap AS next_gap
SELECT b1.ends_at AS start_of_gap
, b2.starts_at AS end_of_gap
, ( UNIX_TIMESTAMP(b2.starts_at) -
/ 3600.00 AS gap_length_hours
FROM bookings AS b1
JOIN bookings AS b2
ON b2.emp_id = b2.emp_id
AND b2.starts_at =
( SELECT MIN(starts_at)
WHERE emp_id = b1.emp_id
AND starts_at > b1.ends_at )
WHERE b1.emp_id = 21
) AS gap_data
WHERE start_of_gap > '2008-12-26 09:00:00'
AND gap_length_hours > 0.5
BY next_gap LIMIT 1
this still needs tweaking for the situation that arises when the last gap is open-ended, i.e. when the LEFT OUTER JOIN returns no next booking