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

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).

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:

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.

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!

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!!!

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!