A Real SQL Mindblower

Hi,

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.

Any ideas?select ...
from Appointments appt1
inner
join Availability avail
on [pseudocode: appt1 + 60 > avail]
inner
join Appointments appt2
on [pseudocode: appt1 + 120 > appt2]
where ...
separarate date and time columns give me the willies (they should be one, a datetime column)

so you will have to work out the DATEADD expressions yourself|||I guess this should be:

select ...
from Availability avail
inner
join Appointments appt1
on [pseudocode: (appt1 + duration) < avail - 60]
inner
join Appointments appt2
on [pseudocode: appt2 - 120 > appt2]
where ...

And this will basically give me a list of available slots that are at least 60 minutes after ANY appointment finishes and at least 120 minutes before ANY appointment starts??|||this: appt2 - 120 > appt2 (your 2nd join condition) will always be false :)

i would definitely want to see some sample rows to try some of this stuff out

i'm still not sure i understand your tables|||I'm not sure I understand my tables sometimes!!

Basically my tables are structured like this:

TABLE: Appointments
ref int identity(1,1), member int, practitioner int, service int, date datetime, time varchar(5), address text, postcode varchar(10), duration int

Relevant Sample Data:
Date: 17/05/04, Time: 14:00, Duration:60

TABLE: Availability
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.|||my condoloences

your varchar time fields make a simple query impossible

as for "sample rows," maybe i should have said "as many sample rows as necessary to illustrate one or two scenarios where the conditions you will be seeking with the query are represented in the data"|||And why does this SQL...

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?|||And why does ... return an instance of each Availability item for every Appointment item that isn't in the same day as it.
because you told it to

... ON appt1.date<>avail1.date|||So, how would I alter that SQL to give me Availability items that doesn't have any appointments in the same day?