I think there are four cases to consider (hopefully the "picture" will come through okay). starttime|----------|endtime The time span in consideration *-----------------* Case 1: ta has mtg that starts before starttime, mtg ends between starttime and endtime 1 *------------------------------* Case 2: mtg starts before starttime, mtg ends after endtime 2 *-----* Case 3: mtg starts and mtg ends between startime and endtime 3 *---------------* Case 4: mtg starts between starttime and endtime, mtg ends after endtime 4
However the four can be reduced to two: a. A meeting starts before the starttime and ends after the starttime (cases 1 and 2) or b. A meeting starts between the starttime and the endtime (cases 3 and 4). So to check if the TA is busy between 17:30 and 18:10 SELECT count(TAID) as total FROM tbl_schedule where TAID = 1 and ((starts <'2005-10-27 17:30' and ends >'2005-10-27 17:30') or (starts >= '2005-10-27 17:30' and starts < '2005-10-27 18:10')) Regards, Donna "C.F. Scheidecker Antunes" <[EMAIL PROTECTED]> 10/27/2005 05:31 PM To mysql@lists.mysql.com cc Subject {Spam?} MySQL and dates puzzle 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.