Glad it worked. Here is how I use it: 1. Fix_Mas_Billing_Overlaps_Step1 lets you pick a date range to search a Master Billing record table for overlaps then creates the view that finds the overlaps. That's the code from below. 2. Fix_Mas_Billing_Overlaps_Step2 simply uses a scrolling region to show all the rows in the view that you just created that are in the date range you picked. There can be any number of rows. I have had over 200 because of lots of errors and screwing around to purposefully break it. You simply select a row for one person and hit a button with this EEP:
EDIT USING Fix_Mas_Billing_Overlaps_Step3 + WHERE (ZID = .vZID) AND EventDate = (.vDate) + ORDER BY EventStartTime ZID is simply the ID # assigned to the person. 3. Fix_Mas_Billing_Overlaps_Step3 also uses a scrolling region but uses the actual data from the Master Billing record table. This will find the records for Bozo (.vZID = 182) on (.vDate = 02/23/2012) and list all the rows that meet that criteria for Bozo. It can be two rows, but it can also easily be 7-8 rows, depending on what Bozo did that day. The scrolling region has several columns that show info (ZID, date, ActivityID, TotalTime, Productivity, TotalPay etc.) so you know where you are, but are not editable. There are two columns StartTime and EndTime that are editable as DB/DateTimePicker. You simply have to adjust the StartTime and EndTime to change the data in the Master Billing record table and eliminate the overlaps. TotalTime is a computed column so this number changes based on you do to the Start/End times. I have made no attempt to do more than one person in Step_3. We run a large production/packaging operation where people in our program get paid for work we subcontract from local companies. So for us an activity can be paid or unpaid. Unpaid is counseling, classroom, community outings, etc. Paid is when something is made for our production customers (make floor displays, package retail products, assemble kits). I have to keep track of both types of activities for billing purposes. Overlaps on Medicaid bills quickly drifts into fraud problems and overlaps on pay gets in to federal and state wage/hour/labor issues, none of which I want. I will send you my forms privately as attachments since attachments are not allowed here. Do with them as you see fit. Tom Frederick President/CEO Elm City Center 1314 W Walnut Jacksonville, IL 62650 W- 217-245-9504 F - 217-245-2350 E - [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Claudine Robbins Sent: Saturday, March 17, 2012 11:14 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Time Overlap I'm happy to report that the code works great in finding overlaps in two rows. I need a little push in the right direction in the logic needed to expand the code to cover an unlimited number of rows with possible overlaps. TIA, Claudine -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Claudine Robbins Sent: Sunday, March 04, 2012 9:11 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Time Overlap I have adapted the code you provided and will test tomorrow with my users. Thanks again. 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 --- 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. ================================================
