Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: How to use Time column to make sure duplicate appointments are not made

    I have start data & end date columns in the table defined as type "Time"

    person x
    start date = 08:00
    end date = 09:00

    person y
    start date = 13:00
    end date = 15:00

    How do i make sure some one doesnt enter
    start date = 06:00
    end date = 14:00

    because it conflicts with existing appointments ?
    Any sql help will be greatly appreciated.

    thanks
    Srik

  2. #2
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32

    Not in the DB you ain't

    You are not going to be able to do this with the DB itself unless triggers or something of the sort could do this (I don't know for sure, but I doubt it). This is "logic" that is usually written into the app you are creating. What programming language are you using? is this for the web. If so I recommend PHP, (some companies use ASP because they are enslaved by MS (ME)).

    Anyway, I'm going over this on paper. Let me know what language you plan on using. I will most likely only be able to help (with specifics) if it's PHP, otherwise I can help with concept (program flow).
    -----------------------------------
    Smith Hayward
    -----------------------------------

  3. #3
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32

    Ok, here's the basic flow

    You'll have to come up with the syntax...

    Here is the concept that I devised based on the possible scenarios with appointment conflicts.

    Given that you have a set appointment that doesn't currently conflict with any other appointments (i.e. the first one you add to the system), there are only two situations that another appointment can be added.

    1. Starts and Ends Before
    2. Starts and Ends After

    This leaves these as the defined conflicts:

    1. Starts at the same time
    2. Ends at the same time
    3. Starts Before and Ends After (stradles existing appt)
    4. Starts Before and Ends During (interrupts)
    5. Starts During and Ends During (contained)
    6. Starts During and Ends After (interrupts)

    By systematically comparing the dates to one another you can detect conflict and report errors to the user with something such as (this is untested PHP, may or may not work)

    Assumed that the existing start and end record have already been pulled from the database result into $result. and newStart and newEnd are values from a formfield of some sort.

    In a loop that checks each appointment record in order do the following validation:

    Code:
    $newDuration = $newEnd - $newStart;
    if($newDuration <= 0){
    $errormsg = "New appointment has invalid duration. Please make sure your End Time is later than your Start Time."
    
    /* Exit Loop & REDIRECT PAGE BACK TO FORM */
    
    }
    else if($result['StartDate']==$newStart){ /* Conflict #1 */
           $errormsg = "New appointment has conflicts, please change START date.";
           exit;
    } 
    
    else if($result['EndDate']==$newEnd){ /* Conflict #2 */
             $errormsg = "New appointment has conflicts, please change END date.";
             exit;
    } 
    
    else if($newStart < $result['StartDate']){ /* New appt starts before : Conflicts #3 and #4 */
             if($newEnd > $result['StartDate']){ /* and Ends either During or After.
        $errormsg = "New appointment has conflicts, End Date must be before $result['Start Date']";
        exit;
    } 
    
    else if($newStart > $result['StartDate']){ /* New appt Starts after existing start */
             if($newStart < $result['EndDate']){ /* but before existing end: Conflicts #5 & #6 */
        $errormsg = "New appointment has conflicts, Cannot start a new meeting in the middle of another one.";
        exit;
    }
    else { /* STORE NEW APPOINTMENT INTO DATABASE */ }
    You could even go one step further and loop through them all and capture the "id" of an appointment, then display a list of conflicting appointments. The options are endless, hopefully this can get you started.
    Last edited by smithhayward; 10-08-03 at 14:02.
    -----------------------------------
    Smith Hayward
    -----------------------------------

  4. #4
    Join Date
    Oct 2003
    Posts
    3
    I use JSP/servlets etc. So you are saying there is no good piece of SQL I can use to do this.. I would like this in the db itself atleast in the form of a trigger. does anyone have code that does this in pl/sql etc ?

    any help is greatly appreciated

    Srik

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by srikb
    I use JSP/servlets etc. So you are saying there is no good piece of SQL I can use to do this.. I would like this in the db itself atleast in the form of a trigger. does anyone have code that does this in pl/sql etc ?

    any help is greatly appreciated

    Srik
    Nope... You CAN have a SQL statement that checks for the 6 conditions mentionned above. It's just a matter of coding it!

  6. #6
    Join Date
    Oct 2003
    Posts
    3

    Thank you guys

    I completed this using jsp code and it works great. I really appreciate all your help.

  7. #7
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Thank you guys

    Originally posted by srikb
    I completed this using jsp code and it works great. I really appreciate all your help.
    Sample code for an appointment that would start at 13:00 and end at: 13:30. If the last select returns a row, it mean you cannot schedule the appointment..

    create table horaire (app_start datetime,
    app_end datetime,
    name varchar(30)) type = Myisam;

    insert into horaire (app_start, app_end, name) values
    ('2003-10-09 13:00:00', '2003-10-09 15:00:00', 'Joe First'),
    ('2003-10-09 17:00:00', '2003-10-09 19:00:00', 'Joe Second'),
    ('2003-10-09 19:00:00', '2003-10-09 20:00:00', 'Joe Third'),
    ('2003-10-09 20:00:00', '2003-10-09 23:00:00', 'Joe Fourth'),
    ('2003-10-10 07:00:00', '2003-10-10 09:00:00', 'Joe Fifth'),
    ('2003-10-10 13:00:00', '2003-10-10 15:00:00', 'Joe Sixth'),
    ('2003-10-13 13:00:00', '2003-10-13 15:00:00', 'Joe Seventh');

    select * from horaire;

    select * from horaire
    where
    ('2003-10-13 13:00:00' = app_start) or /* Starts at the same time */
    ('2003-10-13 13:30:00' = end_start) or /* Ends at the same time */
    ('2003-10-13 13:00:00' < app_start and '2003-10-13 13:30:00' > end_start) or /* Starts Before and Ends After (stradles existing appt) */
    ('2003-10-13 13:00:00' < app_start and ('2003-10-13 13:30:00' between app_start and end_start)) OR /* Starts Before and Ends During (interrupts) */
    ('2003-10-13 13:00:00' between app_start and app_end) and ('2003-10-13 13:30:00' between app_start and end_start)) OR /* Starts During and Ends During (contained) */
    (('2003-10-13 13:00:00' between app_start and app_end) and ('2003-10-13 13:30:00' > end_start)) /* Starts During and Ends After (interrupts) */

    Hope this helps.

    ALWAYS LET THE SERVER DO THE WORK!!!

  8. #8
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32

    Great code

    Wow, that's a great query... I'm still learning all of the INs and OUTs of Database programming. Thanks for elaborating on my theories... at least I know my mind was in the right place, just not the right step of the process.

    Bravo!
    -----------------------------------
    Smith Hayward
    -----------------------------------

Posting Permissions

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