Here is a simplified schema of my database:
Users
------------------------------------
UserID | Name | Assignable
------------------------------------
5 | John Doe | 1
TimeSlots
-------------------------------------------------------
TimeSlotID | StartTime | EndTime
-------------------------------------------------------
3 | 2011-06-30 15:00:00 | 2011-06-30 16:00:00
Appointments
------------------------------------
TimeSlotID | UserID
------------------------------------
3 | 5
I have Users that can be assigned to TimeSlots that make up Appointments. I need a way to start with a start and end time and query all Users that are able to be assigned (Assignable flag set to 1) and have no time conflicts (Appointments that overlap) with those times.
Older TimeSlots will be in there too so you would only be interested in TimeSlots that are now or in the future.
select *
from users u
where u.Assignable = 1
and u.UserID not in (
select UserID
from Appointments a
join TimeSlots t on a.TimeSlotID = t.TimeSlotID
where t.EndTime > now()
and t.EndTime > @desiredStartTime
and t.StartTime < @desiredEndTime
)
edit Taking a cue from tandu
I think this would also work, and it has the added performance benefit of no subqueries:
select *
from users u
left join Appointments a on a.UserID = u.UserID
left join TimeSlots t on (
a.TimeSlotID = t.TimeSlotID
and t.EndTime > now()
and t.EndTime > @desiredStartTime
and t.StartTime < @desiredEndTime
)
where
u.Assignable = 1
and t.TimeSlotID is null
say @start is the start time and @end is the end time, which you pass into the query in your script:
SELECT
UserID
FROM
Users
NATURAL JOIN Appointments a
NATURAL JOIN TimeSlots t1
LEFT JOIN TimeSlots t2 ON (
a.TimeSlotID = t2.timeSlotID
AND (
t2.EndTime BETWEEN @start AND @end
OR t2.StartTime BETWEEN @start AND @end
OR @start > t2.StartTime AND @end < t2.EndTime
)
AND t2.StartTime > NOW()
)
WHERE
Assignable
AND NOW() < t1.StartTime
AND t2.TimeSlotID IS NULL