Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    31

    Unanswered: How to get which time slot is available for this query.

    I have table in my sql with following rows

    SELECT
    AP.DOCTOR_ID,
    AP.PATIENT_ID,
    AP.APPOINTMENTS_DATE,
    AP.08_to_09 ,
    AP.09_to_10 ,
    AP.10_to_11 ,
    AP.11_to_12 ,
    AP.12_to_13 ,
    AP.13_to_14 ,
    AP.14_to_15 ,
    AP.15_to_16 ,
    AP.16_to_17 ,
    AP.17_to_18
    FROM APPOINTMENTS AP

    Which returns following rows for each Dr's appointment. Where 1 means it is "Booked" and 0 means "Available"

    1000000010 2000000001 2017-03-14 00:00:00 1 0 0 0 0 0 0 0 0 0
    1000000010 2000000005 2017-03-14 00:00:00 0 0 0 0 0 1 0 0 0 0

    How can I get response on which appointment is booked and which one is available for this day with this Dr?/

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    something like:

    Code:
    SELECT
     AP.DOCTOR_ID,
     AP.APPOINTMENTS_DATE,
    max( AP.08_to_09) ,
    max( AP.09_to_10) ,
    max( AP.10_to_11) ,
    max( AP.11_to_12) ,
    max( AP.12_to_13) ,
    max( AP.13_to_14) ,
    max( AP.14_to_15) ,
    max( AP.15_to_16) ,
    max( AP.16_to_17) ,
    max( AP.17_to_18)
     FROM APPOINTMENTS AP
    group by  AP.DOCTOR_ID,
     AP.APPOINTMENTS_DATE
    Dave Nance

  3. #3
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    228
    Provided Answers: 1
    Change your model. This is directly heading into the wall! Have a doctors table, and a patients table and an appointments table.

    Appointments (doctor, patient, app_start, app_end, app_date)

    or something similar.

    With the current model you have, you won't be able to deal with appointments that have a different duration than 1 hour, not be able to deal with days for which the clinic has different business hours, etc...

    Besides, finding what you want with a more "relational" model will be soooo easy!

    In other words, revise your model. The current solution you have is *REALLY NOT* how you should do this!

  4. #4
    Join Date
    Feb 2003
    Posts
    31
    Dave,

    Thanks. This works fine. I wanted to if my query shows the column name like 08_to_09 for appointment "1" which is booked.

Posting Permissions

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