Summing total of Overlapping Dates without duplication of days

[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

 

[PWise]

SELECT order AS Expr1, DateDiff("d",MinOfstart,MaxOfend) AS diff
FROM [SELECT orders.order, Min(orders.start) AS MinOfstart, Max(orders.end) AS MaxOfend
FROM orders
GROUP BY orders.order]. AS dt;

 

[MajP]

The correct answers should be 11, and 8
The above query gives 10, and 9
1,2,3,4,5,6,7,8,9,10,11 (11 days)
1,2,3,4,5 8,9,10 (8 days)

Because of the inclusion of start and end dates, I am not confident that this can be done in a query.
This code seems to work on the test case, but try it on other dates.

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

for this example

tblOverlapDates
intOrderID
dtmStartDate
dtmEndDate

 

[szumerspirit]

Thank you both for your help.

I am feeling quite stupid right about now. I have been out of coding for several years, into the data aspect, so I am rusty. I added this code to a module, and redirected my table to use the same name's indicated in the code above. Created a macro to run the code, and it fails telling me it does not recognize 'totalDays'.

I added this as a field, but am afraid I am misreading the code. Please forgive my ignorance. Am I adding this into the DB incorrectly?

 

[PWise]

I would do it this way create a table called DaysOfYear as I have in most databases that I work with with a record for each day of the year

1/1/06
1/2/06
...
12/30/06
12/31/06

select order ,count(*)
from orders
inner join daysofyear on daysofyear.doy between startdate and  enddate
group by order

 

[MajP]

here is a better shot at the code. I found a few exceptions.

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, dtmEndDate DESC"
  rs.Open strSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
  Do While Not rs.EOF
   dtmStart = rs.Fields("dtmStartDate")
   dtmEnd = rs.Fields("dtmEndDate")
  If dtmStart >= dtmOldStart And dtmEnd <= dtmOldEnd Then
    intInterval = 0
  ElseIf dtmStart > dtmOldEnd Then
      intInterval = dtmEnd - dtmStart + 1
      dtmOldStart = dtmStart
      dtmOldEnd = dtmEnd
  ElseIf dtmStart = dtmOldEnd Then
      intInterval = dtmEnd - dtmStart
      dtmOldStart = dtmStart
      dtmOldEnd = dtmEnd
  ElseIf dtmStart <= dtmOldEnd And dtmEnd > dtmOldEnd Then
      intInterval = dtmEnd - dtmOldEnd
      dtmOldStart = dtmOldEnd
      dtmOldEnd = dtmEnd
  End If
    totalDays = totalDays + intInterval
    rs.MoveNext
  Loop
    
End Function

1) You need to make sure all my field names and table names are correct as yours. So edit them in the above code.
2) Drop this function into a module.
3) You can then put this function directly into a query by passing the order ID as a parameter.

SELECT DISTINCT tblOverlapDates.intOrderID, totalDays([intOrderID]) AS [Total Days]
FROM tblOverlapDates;

 

[MajP]

Before putting the function into the code, you should test it in the immediate window.
Just type this
?totalDays(a valid id number)

If you get a value then the fields all match up. Now you can put it in a query.

 

[NvrSaDi]

For a low tech approach...
1) Start with a "make table" grouping query to identify and paste all of the different order numbers into a table.
2) Use VBA and SQL or query to cycle thru this table using the values as criteria to filter records into a second table which consists of all of the same order numbers and dates.
3) Since all records are from the same order, you can use Dmin and Dmax functions for the start and end date fields.
4) Apply a datediff function to the Dmin and Dmax values and paste the results for each cycle into a table with order number and results.