Overlapping%20Date%20Ranges

1
Overlapping Date Ranges

• PAUG Tips Tricks
• September 2002
• Sam Gray

2
Misc Comments

• Dont take notes! This talk will be available
  online (slides and code)
• PAUG website (eventually, maybe)
• My websitehttp//timestream.net/PAUG
• Questions, comments, corrections,
  additionssamgray_at_timestream.net
• Im looking for work!http//timestream.net/resume

3
Why Check For Overlaps?

• Because they shouldnt be there whenever only one
  thing can happen during a given time
• Scheduling application (classes, meeting rooms,
  any limited resource)
• 9/27 800 am 9/27 1200 pm Intro to VB
• 9/27 100 pm 9/27 500 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
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 lt Begin AND StopDate gt Begin
• Partial Overlap 1, Inside 1, Inside 3
• 2. StartDate lt End AND StopDate gt End
• Partial Overlap 2, Inside 1, Inside 2
• 3. StartDate gt Begin AND StopDate lt End
• Outside
• 4. StartDate Begin OR EndDate End
• Identical, Same Start, Same End
• Note If contiguous ranges not OK, change lt to
  lt and gt to gt.

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_at_timestream.nethttp//ti
  mestream.net/PAUG