Bill,
I'm still keeping your code in mind to use for data validation. I'm slowly wrapping my head around the logic needed. Thanks again, Claudine _____ From: [email protected] [mailto:[email protected]] On Behalf Of Bill Downall Sent: Saturday, March 03, 2012 1:51 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Time Overlap Hi, Claudine! I would use a query that names two different copies of the table. The first copy (t1) is the set of rows you are eyeballing one at a time, the second copy (t2) being the source for a list of other rows that overlap. I'm guessing the table has a unique primary key, and that you only care about overlap for a particular vehicle in the query. So something like this: SELECT DISTINCT t1.vehicle + FROM timelog t1 + WHERE EXISTS + (SELECT * FROM timelog t2 + WHERE t1.vehicle = t2.vehicle + AND t1.primarykey <> t2.primary key AND ((t2.starttime < t1.starttime AND t2.endtime >= t1.starttime) + OR (t2.endtime > t1.endtime AND t2.endtime <= t1.endtime)) ) If you can write a query that finds all the bad guys, then with a little work, you should be able to write a similar query that is part of an R:BASE RULE, that will prevent the bad guy from being saved in the first place. Bill On Mar 3, 2012 1:29 PM, "Claudine Robbins" <[email protected]> wrote: Hello all, I'm trying to programmatically identify overlapping hours in sets of start/end time records for a maximum 24 hour period for vehicle usage, something that the human eye readily sees such as: Start End 10:00:00 12:00:00 9:00:00 13:00:00 Where the second row overlaps the first by 2 hours (1 hour earlier and 1 hour later) so that I could visually see the allowable time segment as the second one and disregard the first. In the following example, however: Start End 10:00:00 12:00:00 08:00:00 11:00:00 I have to take the first row into account with 1 hour later end time than the second row and 2 hours earlier than the first start time. I can still see that the result is allowing the end time of the first row and the start time of the second row. It gets complicated where there are multiple rows and partial minutes involved thus the need to be able to calculate the total time shared by all the rows without making the user go blind trying to visualize the overlapping hours. Best scenario would be to prevent the user from adding an overlapping segment at data entry validation. Typical entry example: Date Start End Vehicle 01/01/12 5:00:00 11:30:00 234 01/01/12 11:30:00 18:00:00 234 01/01/12 17:30:00 0:00:00 234 Perhaps someone on the list has already tackled this problem or someone can give me a hint in what direction to take my calculations. Thank you very much in advance, Claudine --- RBASE-L ================================================ TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected] (Don't use any of these words as your Subject: INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: INTRO ================================================ TO UNSUBSCRIBE: Send a plain text email to [email protected] In the message SUBJECT, put just one word: UNSUBSCRIBE ================================================ TO SEARCH ARCHIVES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: SEARCH-n (where n is the number of days). In the message body, place any text to search for. ================================================
