I'm trying to select the first ten empty time slots between appointments in a MySQL database.
The appointment table has basically 3 fields: appointment_id INT, startDateTime DATETIME and endDateTime DATETIME.
We can imagine some data like this (for simplicity's sake, I've left the date part out of the datetime so let's consider these hours are in the same day). Also the data is ordered by startDateTime:
4 | 09:15:00 | 09:30:00
5 | 09:30:00 | 09:45:00
8 | 10:00:00 | 10:15:00
3 | 10:30:00 | 10:45:00
7 | 10:45:00 | 11:00:00
2 | 11:00:00 | 11:15:00
1 | 11:30:00 | 12:00:00
So my goal is to extract:
00:00:00 | 09:15:00
09:45:00 | 10:00:00
10:15:00 | 10:30:00
11:15:00 | 11:30:00
In ended up doing this:
SET @myStart = '2012-10-01 09:15:00';
SET @myEnd = NULL;
SET @prevEnd = NULL;
SELECT a.endDateTime, b.startDateTime, @myStart := a.endDateTime
FROM appointment a, appointment b, (
SELECT @myEnd := min(c.startDateTime)
FROM appointment c
WHERE c.startDateTime >= @myStart
ORDER BY startDateTime ASC
) as var ,
(SELECT @prevEnd := NULL) v
WHERE a.appointment_id = (
SELECT appointment_id
FROM (
SELECT appointment_id, max(endDateTime), @prevEnd := endDateTime
FROM appointment d
WHERE (@prevEnd IS NULL OR @prevEnd = d.startDateTime)
AND d.startDateTime >= @myEnd
) as z
)
AND b.startDateTime > a.endDateTime
ORDER BY b.startDateTime ASC LIMIT 0,10;
This doesn't return any result. I guess it's because of an incorrect initialization of my user defined variables (just discovered them and I may be using them completely wrong).
If I run only the first subquery whose goal is to initialize @myEnd at the first appointment after @myStart, I can see that it in fact returns 09:15:00.
The second subquery (SELECT @prevEnd := NULL) v is meant to set @prevEnd back to NULL each time a row is selected in the main query. I'm not quite sure it works like that...
The last subquery is meant, starting with a null @prevEnd and an initialized @myEnd, to select the appointment after which there is a gap. I could verify that it works too if separated from the rest of the query.
Do you have any advice on what I could do to fix the query, on how I could/should do it otherwise or on wheter it's even possible or not?
Thanks very much in advance.
Edit: I have edited it like this:
SELECT *
FROM (
SELECT COALESCE( s1.endDateTime, '0000-00-00 00:00:00' ) AS myStart, MIN( s2.startDateTime ) AS minSucc
FROM appointment s1
RIGHT JOIN appointment s2 ON s1.endDateTime < s2.startDateTime
AND s1.radiologyroom_id = s2.radiologyroom_id
WHERE s1.startDateTime >= '2012-10-01 00:00:00'
AND s1.radiologyroom_id =174
AND s1.endDateTime < '2013-01-01 00:00:00'
GROUP BY myStart
ORDER BY s1.startDateTime
)s
WHERE NOT
EXISTS (
SELECT NULL
FROM appointment
WHERE startDateTime >= myStart
AND endDateTime <= minSucc
AND radiologyroom_id =174
ORDER BY startDateTime
)
and it retrieves 369 rows in 14.6 seconds out 6530 records
If there are no gaps between ids, and id is always increasing, you could use this:
SELECT coalesce(s1.endDateTime, '0000-00-00 00:00:00'), s2.startDateTime
FROM
slots s1 right join slots s2
on s1.appointment_id=s2.appointment_id-1
WHERE coalesce(s1.endDateTime, '0000-00-00 00:00:00')<s2.startDateTime
LIMIT 10
EDIT: you can also try this:
SELECT * FROM
(SELECT
coalesce(s1.endDateTime, '0000-00-00 00:00:00') as start,
min(s2.startDateTime) minSucc
from slots s1 right join slots s2
on s1.endDateTime<s2.startDateTime
group by start) s
WHERE
not exists (select null
from slots
where startDateTime>=start
and endDateTime<=minSucc)
EDIT2: I admit that I am not much pratical with queries with variables, but this looks like that it could work:
select d1, d2 from (
select
@previous_end as d1,
s.startDateTime as d2,
@previous_end:=s.endDateTime
from (select startDateTime, endDateTime from slots order by startDateTime) s,
(select @previous_end := '0000-00-00 00:00:00') t) s
where d1<d2