Sum time but ignore overlapping events

[fnem]

Hi,



I have a table which contains information about issues that affect a piece of hospital equipment. Each issue will have a start time and end time as well as other attributes, one of which is a simple boolean determining whether the machine was down during the issue.
It is possible for multiple issues to affect a machine simultaneously.
I would like to run a query that sums the duration of the issues that have downtime during a period but ignores any overlapping downtime events.

A simplified version of my table would be something like:

Issue----Machine---Start Time-----------End Time-------------Duration---Downtime?

1---------A----------01/11/2015 08:00---01/11/2015 10:00---120mins----yes
2---------A----------01/11/2015 09:00---01/11/2015 11:00---120mins----yes
3---------A----------02/11/2015 08:00---02/11/2015 15:00---420mins----no

In this case total downtime for machine A would be 240mins but I would want to report 180mins to eliminate the overlap between issues 1 and 2.

Hope this makes sense. So far I have constructed my database in Access with very little code and I would like to keep the solution as simple as possible.

Thanks

[June7]

EndTime of issue 2 minus StartTime of issue 1 = 180 minutes.

But how to determine that these records overlap? What if there are a dozen overlapping issues? How to determine the 'first' and 'last' issues of a continuous period?

Personally, I don't think this can be done without VBA. Sorry, not something I want to tackle.

[NTC]

As J7 implies; it is a very complex design to set up the logic that will work in all scenarios - presuming unlimited number of events per day and in random order. It is do-able for sure. Just not the type of question that lends itself to air code (untested off the cuff info)....and would take quite a bit of time to refine.....

[fnem]

ok well I've solved this problem and here is my solution for anyone that encounters the same issue. As mentioned this is not possible without code, so here it is:

'Called when running downtime report to provide sum of downtime
Public Function dtsum()
 
    Dim dbs As Database
    Set dbs = CurrentDb
             
    Dim rst As DAO.Recordset
   
    'Start date/time of first downtime event being inspected
    Dim start1 As Date
    'Start date/time of second downtime event being inspected
    Dim start2 As Date
    'Finish date/time of first downtime event being inspected
    Dim fin1 As Date
    'Finish date/time of first downtime event being inspected
    Dim fin2 As Date
    'Calculated downtime in days
    Dim dtdays As Double
    'Calculated downtime in minutes
    Dim dt As Integer 
    'Dates must be in American format to work
    Dim correctformatstart As Date
    Dim correctformatfinish As Date
        
    'Set the start and finish dates of assessed period to American format
    correctformatstart = Format(DateValue(Forms![report selection].[Start Date]), "mm/dd/yyyy")
    correctformatfinish = Format(DateValue(Forms![report selection].[Finish Date]), "mm/dd/yyyy")
        
           
    'Ensure no existing data
    strsqltasks = Empty
   
    'Create temp DB with start and finish dates from Issues table for each issue where machine down = true, machine = that selected on form, where the start date of the issue falls within the start and finish dates defined on the form
    strsqltasks = "SELECT [Start date], [finish date] FROM issues where [machine down] = true and issues.[machine] = """ & Forms![report selection].machine & """ and issues.[start date] between #" & correctformatstart & "# and  (#" & correctformatfinish & "#)+0.9999 ORDER BY [start date];"
   
    'take first row of temp DB
    Set rst = dbs.OpenRecordset(strsqltasks, dbOpenDynaset)
   
    'Ensure no old data stored in variables
    dtdays = 0
    dt = 0
 
    'If not in last row of temp DB
    If Not rst.EOF Then
   
    'Define start and finish of issue on row
    start1 = rst![Start Date]
    fin1 = rst![Finish Date]
                     
        'add duration of first issue to downtime total
        dtdays = dtdays + fin1 - start1
                              
        'move to the next row of temp DB
        rst.MoveNext
       
        'If not at end of temp DB
        Do While Not rst.EOF
       
        'set start and finish of 2nd issue
        start2 = rst![Start Date]
        fin2 = rst![Finish Date]
       
            'if 2nd issue overlaps first
            If start2 < fin1 Then
                'if 2nd issue falls entirely within 1st issue don't add any downtime to total
                If fin2 < fin1 Then
                dtdays = dtdays
                Else
                'if 2nd issue overlaps 1st but extends beyond then add bit that extends to downtime total
                dtdays = dtdays + (fin2 - fin1)
                End If
            Else
            'if no overlap then add entire issue duration to downtime total
            dtdays = dtdays + (fin2 - start2)
            End If
           
        'Set the start and finish of first issue to current row of temp DB
        start1 = rst![Start Date]
        fin1 = rst![Finish Date]
        'Go to the next row of the temp DB
        rst.MoveNext
               
        Loop
    End If
           
    'define dt in minutes
    dt = dtdays * 24 * 60
   
    'clear variables
    strsqltasks = Empty
    start1 = 0
    start2 = 0
    fin1 = 0
    fin2 = 0 
End Function

[NTC]

It is always nice when someone follows up and provides code to the group, thank you.

As you have used the loop approach - if the file size is large - one may find that query run time is unacceptably long. The other approach is using record sets (queries) and will be faster. A complex design such as you need will typically require a design that goes beyond using the query design view feature and require a pure sql view coded query.

[fnem]

I have no doubt that you are right, this could slow right down if munching through a lot of data.

My first solution was to make a temporary table with each minute of time as the primary key and then with code add a boolean to a second field for every minute of downtime in the assessed interval. Because the primary key can't have duplicates only one instance of a minute could ever exist and so overlap could be avoided. This really was a slow process though and would 'think' for a number of seconds before working.

Fortunately the nature of my database includes only a small number of downtime events every month and so even when run for several years it is almost instant.

[orange]

I'm curious re How to interpret your data:

1---------A----------01/11/2015 08:00---01/11/2015 10:00---120mins----yes
2---------A----------01/11/2015 09:00---01/11/2015 11:00---120mins----yes
3---------A----------02/11/2015 08:00---02/11/2015 15:00---420mins----no

How could machine A be "down" from 8 until 10, AND also from 9 until 11 as two separate events?
If the machine is down between 8 and 10, it seems that it would be down at 9.
Why record two events? Seems more logical to have down time start and down time end and recording when the machine was taken off line (from service) until it went back online(in service).

What exactly is downtime?

[fnem]

This is a simplified version of the data for illustrative purposes only. Essentially each record is an issue afflicting the machine, downtime is an attribute that might apply to an issue if that issue prevents treatment. A machine can have multiple concurrent issues and so potentially multiple concurrent instances of downtime, unlikely but I need to allow for it. Perhaps downtime is the wrong expression, 'treatment preventing' might be more appropriate. Downtime being the sum of unique 'treatment preventing' occurences.