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