Overlapping times

[shift2076]

Here's a good one for the experts...

I have a query that sums downtime on equipment. The problem that I came across is that there can be multiple status items open up on the same piece of equipment. When the restored time on one status item occurs after the down time of another, the query still adds the total down time of both status items which is giving me greater downtime than there actually is.

Is there a workaround for this?

Thanx,
MJ

 

[Alc]

There may well be multiple ways of doing this but I've used the following in the past:

1) Find the start time for each status item on a given piece of equipment
2) Loop through each of these start times and find the overall earliest
3) Find the end time for each status item on a given piece of equipment
4) Loop through each of these end times and find the overall latest
5) The difference between these two is the actual overall down time

Hope that makes sense.

 

[shift2076]

That makes perfect sense. Don't know why I didn't think of it.

Thank you!

 

[shift2076]

For anyone else searching for the answer to a similar problem, I just used the MIN and MAX function on the dates. Didn't even have to select the systems as 'DISTINCT' in the query.

Thanks again for the help!