Hello all,

I have a TA table to record TA UNAVAILABLE times. This table is quite simple. It has a TAID number, a start date and an end date.

tbl_schedule { TAID integer, starts datetime, ends datetime }

A valid entry would be a TA whose id is 1 and between 17:00 and 18:00 he is busy. So: 1,'2005-10-27 17:00:00','2005-10-27 18:00:00'

Each ta can have more than one entry per day. He might be a busy TA and have a lot of meetings scheduled. The meetings do not have to be 1 hour length, they can be 5 or 10 minutes. So something like this would also be valid: 1,'2005-10-27 17:05:00','2005-10-27 17:10:00'

Now, I need to check, given a start and end dates, if that would overlap with some record already present in the database. If I want to know if the TA is busy between 17:30 and 18:10 I could I issue something like this:

SELECT count(TAID) as total FROM tbl_schedule where (TAID = 1) AND (('2005-10-27 17:30' BETWEEN starts AND ends) OR ('2005-10-27 18:10' BETWEEN starts AND ends))

It would return a number not zero as total if the dates are between the registered database. However, this does not work properly. Here's a case when it does not work: Say I want to check between 14:00 and 20:00. The TA is busy from 17:00 and 18:00, hence if I try to schedule a meeting that will go from 14:00 to 20:00 with the statement above it would return 0 as total. This is not good because I need to know that given 14:00 to 20:00 that would not overlap with any previous engagement on the database. Since the TA is busy from 17:00 to 18:00 I must know that I cannot schedule anything like that.

Can anyone help me on this issue? How can I check given a start and end datetime that it does not overlap with what is in the database?

Thanks,

C.F.