Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: crteating quieries for oracle

    we want to be able to run a query which analysis the data inputed in the table for craeting an appointment, to make sure dates and times are not inouted twice (overbooked) and that dates & times dont clash with office hours any ideas?

    cheers

    lucy (new to dbforums)

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Post the description of the tables and what you are looking for ...

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi lucky,

    you can run the following query.

    Say, your table name is Appointment .I have not tested and run this query but it give you idea and you can formulate it according to your requirment. Please correct this if it has any Syntax error.

    SELECT *
    FROM appointment
    WHERE EXISTS (
    SELECT *
    FROM appointment
    WHERE TO_DATE (app_date, 'HH24:MI') BETWEEN 9:00 and 17:00)
    UNION
    SELECT *
    FROM appointment
    WHERE EXISTS (
    SELECT COUNT (*)
    FROM appointment
    GROUP BY app_date
    HAVIN COUNT (*) > 1);
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi lucky,

    you can run the following query.

    Say, your table name is Appointment .I have not tested and run this query but it give you idea and you can formulate it according to your requirment. Please correct this if it has any Syntax error.

    SELECT *
    FROM appointment
    WHERE EXISTS (
    SELECT *
    FROM appointment
    WHERE TO_DATE (app_date, 'HH24:MI') BETWEEN 9:00 and 17:00)
    UNION
    SELECT *
    FROM appointment
    WHERE EXISTS (
    SELECT COUNT (*)
    FROM appointment
    GROUP BY app_date
    HAVIN COUNT (*) > 1);
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    hope this makes sense

    our appointment table

    Name Null? Type
    ----------------------------------------- -------- ------------------
    APPOINTMENTID NOT NULL NUMBER(8)
    OPTICIANID NUMBER(1)
    STATUSID NUMBER(1)
    ROOMID NUMBER(1)
    PATIENTID NUMBER(6)
    APPOINTMENTDATE VARCHAR2(8)
    APPOINTMENTTIME VARCHAR2(5)
    APPOINTMENTTYPE CHAR(50)
    ENDTIME VARCHAR2(5)

    when we enetr new patient details we want (on the form end when inserting the new info) we dont want any dates or times to clash with other patients i.e time = 12:02 when we input a new appointment for a patient and enter 12:02 we wnat it to not allow us to do this as would clash with an another appointment. Also at 12.30 in the senario the opticians r supposed to got to lunch for an hour so when we input the appointmnet type i.e. contact lens fitting (which lasts for 30mins) if the time should run into 12.30 then this appointment cant be made. Hope it makes sense

  6. #6
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi lucky,

    you can run the following query.

    Say, your table name is Appointment .I have not tested and run this query but it give you idea and you can formulate it according to your requirment. Please correct this if it has any Syntax error.

    SELECT *
    FROM appointment
    WHERE EXISTS (
    SELECT *
    FROM appointment
    WHERE TO_DATE (app_date, 'HH24:MI') BETWEEN 9:00 and 17:00)
    UNION
    SELECT *
    FROM appointment
    WHERE EXISTS (
    SELECT COUNT (*)
    FROM appointment
    GROUP BY app_date
    HAVIN COUNT (*) > 1);
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  7. #7
    Join Date
    Nov 2003
    Location
    england
    Posts
    95
    appointment table

    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    APPOINTMENTID NOT NULL NUMBER(8)
    OPTICIANID NUMBER(1)
    STATUSID NUMBER(1)
    ROOMID NUMBER(1)
    PATIENTID NUMBER(6)
    APPOINTMENTDATE VARCHAR2(8)
    APPOINTMENTTIME VARCHAR2(5)
    APPOINTMENTTYPE CHAR(50)
    ENDTIME VARCHAR2(5)

    optician

    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    OPTICIANID NOT NULL NUMBER(1)
    OPTICIAN CHAR(40)

    Room

    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ROOMID NOT NULL NUMBER(1)

    when u add in a new appointment for a patient we want to make sure that at the time booked in for that the optician, room and time are all free of clashing with other appointments already in the database. Also the optician will go on lunch for an houe at 12.30 so any appointmenst b4 then have to be finished by 12.30:

    list of appointments types and times are currently not in the db example in our drop down list in oracle forms we have such appointment types as
    sight test (10 mins), eye examination (15 min), spectacle fitting (20 min), contact lens fitting(30 minutes), we want to make sure that no more appointment can be entered if for example the time is 12.22, 12.16, 12.11, 12.01 etc

Posting Permissions

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