Tom, I can't wait to try out the code. I'm going blind from trying to find overlaps visually just while testing all my options.
Will let you and the list know what works for me. Thank you SO MUCH! Claudine -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of TFred Sent: Saturday, March 03, 2012 6:23 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Time Overlap Claudine, I had the exact same problem about 4 years ago: Needed to find overlapped times to avoid Medicaid billing errors. Nothing I tried worked. While attending the R:Base conference that year I asked several developers with far more experience for suggestions. Steve from California must have taken pitty because he sat down with me during a break and in about 15 minutes came up with the following code. I put this into a 3 form set. 'Fix_Mas_Billing_Overlaps_Step1' sets the vDateStart and vDateStop so I could limit the days I wanted to review. This code runs off a button after the dates are set. The next form, ' Fix_Mas_Billing_Overlaps_Step2', lets me pick a line IDed as having an overlap. The next form 'Fix_Mas_Billing_Overlaps_Step3' form pulls up all the lines associated with that line and allows me to edit the Event time fields of those rows as needed to eliminate the overlap. I use this all the time and it has always found the overlaps regardless of how many rows it searches. Hope this works for you. I could not thank Steve enough. Tom Frederick Jacksonville, IL SET ERROR MESSAGE 2038 OFF DROP TABLE Datarun SET ERROR MESSAGE 2038 ON PROJECT Datarun FROM MasterRecord USING + ZID, EventType, ActID, EventDate, EventStartTime, + EventEndTime + WHERE EventDate >= .vDateStart + AND EventDate <= .vDateStop ALTER TABLE Datarun ADD ET_ID INTEGER AUTONUM ET_ID IN Datarun USING 100. 1. + ORDER BY ZID, EventDate, EventStartTime, EventEndTime NUM SET ERROR MESSAGE 2059 OFF SET ERROR MESSAGE 2038 OFF DROP VIEW vDatarun2 SET ERROR MESSAGE 2038 ON CREATE VIEW vDatarun2 (ET_ID, ZID, EventType, ActID, + EventDate, EventStartTime, EventEndTime) + AS + SELECT v1.ET_ID, v1.ZID, v1.EventType, + v1.ActID, v1.EventDate, v1.EventStartTime, + v2.EventEndTime + FROM Datarun v1, + Datarun v2 + WHERE v1.ET_ID = (v2.ET_ID + 1) + AND v1.ZID = v2.ZID + AND v1.EventDate = v2.Eventdate + AND v1.EventStartTime < v2.EventEndTime + ORDER BY v1.ZID, v1.Eventdate COMPUTE vCurrStartDate AS MINIMUM EventDate FROM vDatarun2 COMPUTE vCurrEndDate AS MAXIMUM EventDate FROM vDatarun2 SET ERROR MESSAGE 2059 ON EDIT USING Fix_Mas_Billing_Overlaps_Step2 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Claudine Robbins Sent: Saturday, March 03, 2012 4:41 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Time Overlap Hey Ken! Sounds like just what I need! Claudine -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Ken Shapiro Sent: Saturday, March 03, 2012 4:14 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Time Overlap Claudine, I have a routine written in transact-sql, easily translatable to RBASE that identifies overlapping time periods. I have to dig out the code and post it. But it identifies time periods where the start time and/or end time overlaps a set of times within the database. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Claudine Robbins Sent: Saturday, March 03, 2012 1:33 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Time Overlap 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
