Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23

    Unhappy Unanswered: 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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    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??

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    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?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by paulbrooks
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    So, how would I alter that SQL to give me Availability items that doesn't have any appointments in the same day?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •