I'm trying to work out how to extract the information that I need from a set of database tables and can't think of a way of doing it with SQL.
The database forms the basis for a diary/calendar system for part-time employees and has two tables:
- One is called 'Availability' and holds info on each available hour slot in the calendar. The table just has fields 'time' and 'date', where the time is an integer representing an hour and date is datetime. The calendar runs from 07.00 to 23.00 each day, so there could be 16 rows in 'Availability' for one day. If any part of a day is unavailable (i.e. the employee doesn't work then) there will be no corresponding rows in the table.
- The second table is 'Appointments', which holds details of appointments that the employee is booked for. The main fields are 'date', 'time' and 'duration' (integer for minutes). All appointments will cover a time span that is also covered by an available period, but they are not actually linked in any way.
I need an SQL query that will return all available time slots that start at least 60 minutes after any appointments have FINISHED and at least 120 minutes before any appointment STARTS.
Since there is no link between the 'Appointments' table and the 'Availability' table, I can't think of any way of doing this.
ref int identity(1,1), practitioner int, date datetime, time varchar(5)
Relevant Sample Data:
Date: 17/05/04, Time: 07:00
An appointment can only be added to the database if there is an available period(s) for the chosen time (so each appointment will cover the periods represented by one or more item in the Available table).
So, before I add an appointment I need some SQL that will give a list of available periods that aren't yet covered by appointments.
SELECT avail1.ref, avail1.time, avail1.date FROM (Availability avail1 INNER JOIN Appointments appt1 ON appt1.date<>avail1.date AND appt1.practitioner=avail1.practitioner) WHERE avail1.practitioner=1 ORDER BY avail1.date, avail1.time
...return an instance of each Availability item for every Appointment item that isn't in the same day as it. Surely it should only return an item if NO APPOINTMENTS are in the same day as it?