1 / 9

Efficient Date Range Overlap Detection Method

Learn how to detect and handle overlapping date ranges efficiently with tips and tricks provided in this informative talk. Identify conflicting date ranges in various scenarios like scheduling applications and billing systems with ease. Comprehensive guide by Sam Gray.

fuller
Download Presentation

Efficient Date Range Overlap Detection Method

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Overlapping Date Ranges PAUG – Tips & Tricks September 2002 Sam Gray

  2. Misc Comments • Don’t take notes! This talk will be available online (slides and code) • PAUG website (eventually, maybe) • My website:http://timestream.net/PAUG • Questions, comments, corrections, additions:samgray@timestream.net • I’m looking for work!http://timestream.net/resume

  3. Why Check For Overlaps? • Because they shouldn’t be there whenever only one thing can happen during a given time: • Scheduling application (classes, meeting rooms, any limited resource) 9/27 8:00 am 9/27 12:00 pm Intro to VB 9/27 1:00 pm 9/27 5:00 pm Advanced VB • Billing applications (various rates applied over time), e.g., health insurance costs: 1/1/2001 12/31/2001 $200/month 1/1/2002 12/31/2002 $300/month

  4. How Do You Find Them? • My first attempt 3 years ago was extremely messy (18 lines of WHERE criteria!) and complex (and only worked for one table). • Revisited problem recently. First mapped all possible relationships between date ranges. • Then constructed WHERE criteria to catch the conflicts.

  5. Date Range Conflicts • Contiguous (?) • Partial overlap (2) • Inside (3) • Outside • Identical • Same Start/End

  6. 9/27/2001 11:30 am Contiguous Ranges (Special Case) • Contiguous: “touching along a boundary.” One period starts at the exact same time another ends. • Generally OK for: Scheduling rooms, classes, etc – when working with times. • Not OK for: Most financial applications – when working with days.

  7. These Four Query Criteria Should Find All Conflicts StartDate, StopDate: Field names in table Begin, End: New values being tested for conflicts 1. StartDate < Begin AND StopDate > Begin Partial Overlap 1, Inside 1, Inside 3 2. StartDate < End AND StopDate > End Partial Overlap 2, Inside 1, Inside 2 3. StartDate > Begin AND StopDate < End Outside 4. StartDate = Begin OR EndDate = End Identical, Same Start, Same End Note: If contiguous ranges not OK, change < to <= and > to >=.

  8. Leads to... Generalized Procedure Ugly function call, but extremely reusable: Public Function DateRangesOverlap( _ sTableName As String, _ sStartFieldName As String, dtStartDate As Date, _ sEndFieldName As String, dtEndDate As Date, _ sIDFieldName As String, _ Optional lngIDValueToExclude As Long, _ Optional bAllowContiguousRanges As Boolean = True, _ Optional sOtherCriteria As String, _ Optional rsIDs As ADODB.Recordset) As Long [~100 lines of code] End Function

  9. Demo/The End • See DateRanges.mdb for demo code • Thanks!Sam Graysamgray@timestream.nethttp://timestream.net/PAUG

More Related