100 likes | 160 Vues
Overlapping Date Ranges. PAUG – Tips & Tricks September 2002 Sam Gray. 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
E N D
Overlapping Date Ranges PAUG – Tips & Tricks September 2002 Sam Gray
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:firstname.lastname@example.org • I’m looking for work!http://timestream.net/resume
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
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.
Date Range Conflicts • Contiguous (?) • Partial overlap (2) • Inside (3) • Outside • Identical • Same Start/End
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.
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 >=.
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
Demo/The End • See DateRanges.mdb for demo code • Thanks!Sam Graysamgray@timestream.nethttp://timestream.net/PAUG