Summing total of Overlapping Dates without duplication

[szumerspirit]

Hello, I am trying to create a SQL query or VBA code in Access 2003 which will allow me to find multiple records for an order, sum the interval between the start and end dates of those records. Where overlapping dates exist, it would sum the total days from the earliest day to the latest day without duplication of days, such as:

Order Start End

1 5/1/06 5/6/06
1 5/4/06 5/10/06
1 5/3/06 5/11/06
2 5/1/06 5/5/06
2 5/8/06 5/10/06

Resulting With

Order Total Days
1 (11)
2 (7)

Each "Order" could have any number of records. They could overlap or not. If I do a straight sum, clearly Order #1 would come back with duplication of days, this is the scenario I am trying to avoid. I do not know If I just haven't had enough coffee yet, but I cannot seem to get my head around this!

I know I cannot be the first person who has come up against this problem, and was wondering if someone could point me in the right direction. My ultimate need is to subtract the final number from an Overall interval, so using either a query or code to even create a table which I could then link for the remaining calculations would work just fine for me.

Any assistance would be greatly appreciated!

Thanks

 

[MajP]

Try something like this. I get the right answers for you test cases, but I do not know if I considered everything.

Public Function totalDays(intOrderID As Integer) As Long
  Dim rs As New ADODB.Recordset
  Dim strSql As String
  Dim dtmStart As Date
  Dim dtmEnd As Date
  Dim dtmOldStart As Date
  Dim dtmOldEnd As Date
  Dim intInterval As Integer
  
  strSql = "SELECT * FROM tblOverlapDates WHERE intOrderID = " & intOrderID & " ORDER BY dtmStartDate"
  rs.Open strSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  
  Do While Not rs.EOF
    dtmStart = rs.Fields("dtmStartDate")
    dtmEnd = rs.Fields("dtmEndDate")
  If dtmStart > dtmOldEnd Then
      intInterval = dtmEnd - dtmStart + 1
      dtmOldStart = dtmStart
  ElseIf dtmStart = dtmOldEnd Then
      intInterval = dtmEnd - dtmStart
      dtmOldStart = dtmStart
  ElseIf dtmOldEnd >= dtmStart And dtmEnd < dtmOldEnd Then
      intInterval = dtmEnd - dtmOldEnd
      dtmOldStart = dtmOldEnd
    End If
    dtmOldEnd = dtmEnd
    totalDays = totalDays + intInterval
    rs.MoveNext
  Loop
    
End Function

By the way you answer should be
Order Total Days
1 (11)
2 (8) not (7)


1,2,3,4,5,8,9,10