Mean Time Between Failure

[bfreese1972]

I'm fairly new to Access, so maybe someone can school me here...

I have a issue tracking database query reporting a serial number in the first column, an open issue date in the second column and close issue date in the third column. There can be several instances of each serial number.

I already figured out how to use DateDiff to get a time to fix column...

I want to have a column reporting the difference between the previous close date to the next open date (grouped by serial number).

Example
S/N Open Date Close Date Time to Fix MTBF
1234 1/10/2007 1/13/2007 3 5
1234 1/1/2007 1/5/2007 4
2222 1/2/2007 1/3/2007 1

 

[antoka05]

You may try to use this query>

SELECT [s/n], Max(open_date) - Min(close_date) AS Date_Difference
FROM myTable
GROUP BY [s/n];

This query doesn't work if you have more than two records for each s/n.

Regards,
Antonio

 

[Len Boorman]

Now I am not an expert on MTBF but was asked to do this myself and was told that the method of calculation was: (Tried to put it into your situation)

(Latest Open Date minus Earliest Open Date)/Number of Open dates

Took a number of queries to get there

Query 1 Select Min Open Date and S/N
Query 2 Select Max Open Date and S/N
Query 3 Calculate MaxOpen-MinOpen by S/N (from 1 and 2)
Query 4 Count Number Open Dates by S/N
Query 5 MTBF= Query3/Query4 basically

HTH

L

 

[Zigzag]

Mtbf

There are several ways of calculating Mean Time Between Failure.

The easiest is.....

1. Calculate number of minutes of a date range.
2. Count the incident of downtime durring that date range.
MTBF = 1/2

Also as Len showed in the last post, this date range can be norrowed down to the last open date and eariest open date.

It all depends on what you are attempting to prove/demonstrate with this info.

Garry Smith

 

[The_Doc_Man]

This is surprisingly hard because it involves an attempt to cross-reference records from a table. As any avid reader of this forum knows, that cannot happen because there is no such thing as "the next record" in a query.

Having said that, there is a way to do this that does not involve that much work.

Write a query that does a GROUP BY of your serial number. You have enough data to do this because you have down time and up time in each incident record. So...

For this query, compute the down-time for each entry as uptime-downtime for that incident record. BUT include the uptime and downtime in the query too.

Next, you need a DCount of records for that S/N, a DMAX of the return-to-service time, a DMIN of the went-out-of-service time, and a DSum of the computed down time - all for the records grouped by serial number.

MTBF then equals ( ( DMAX(return-to-service time) - DMin(out-of-service time) ) - DSum(down time) / DCount - 1

Alternatively, instead of using DMax(return-to-service time), use NOW() and use DCount of down events. BUT you might have to do this as a two-parter by filtering out any records having an out-of-service time but no entry for return-to-service.

Since you have that DSum and the DCount, you can also compute MTTR (Mean Time to Repair.)

Here's the logic. MTTR = sum of individual down times / number of down times

and that is computable with the data you have on hand.

MTBF = sum of individual up time / number of up times

You know the time of the first record in your list. That gives you the device's first down time. You know the time it was last returned to service. This interval is the total recorded time that you "know" about this device. The amount of up time is just the difference between the first time you know and the last time you MINUS the accumulated down time. The number of up times depends on an issue not yet described here, but basically, if the item is up now, it has as many up time intervals as it had down-time intervals, or if you don't want to say "NOW" then it has one less up time than the number of down times.

Now, if you happened to know when the item first went into service, your MTBF and MTTR can be computed using this same logic and at worst a UNION query.

Number of up times would then be (number of down-events + 1) if it is up right now or (number of down-events) if it is down right now. Elapsed time is still the difference between the earliest and latest times you "knew" the device. And UP time is just the difference between the "known" time and the accumulated down time.

Hope you followed that.

 

[Zigzag]

Hi Doc Man,

Slighly off the thread for a moment, why are you taking into consideration the downtime when calculating the MTBF?

"MTBF then equals ( ( DMAX(return-to-service time) - DMin(out-of-service time) ) - DSum(down time) / DCount - 1"

Mean time between failure should be a reflection of the machine performance. If the engineer took 1 day to respond to the breakdown, 1 day to locate the parts and another day to actualy fix then this is not related to the performance of the machine?
If this time is taken out then the performance (MTBF) of the machine appears worse when in fact it is poor performance of the support service provided.

Sorry for the "Off Thread question"

Garry

 

[The_Doc_Man]

You know the total time of existence and the total down time, so have to compute the up time - which is total time minus down time. From total up time you can compute MTBF as total up time divided by "episodes" when out of service.

You are quite right - you don't count down time in an MTBF computation. Down time contributes to MTTR. It is just that the data as described supports direct computation of DOWN time. So it was necessary to back-door compute the UP time. That is why down time appears in the MTBF.

Not off thread at all.

 

[EMP]

I already figured out how to use DateDiff to get a time to fix column...

I want to have a column reporting the difference between the previous close date to the next open date (grouped by serial number).

Example
S/N Open Date Close Date Time to Fix MTBF
1234 1/10/2007 1/13/2007 3 5
1234 1/1/2007 1/5/2007 4
2222 1/2/2007 1/3/2007 1

Though the title of the thread is Mean Time Between Failure, it seems what the OP wants is the number of days between the previous close date and the open date. In the example, 5 (days) is given as between 1/5/2007 and 1/10/2007.

^

 

[Zigzag]

Thanx Doc Man,

Many faults with MTBF statistics.

If a machine breaks down twice in January and then runs for the rest of the year, and a MTBF report is run in December for the last year, the MTBF will appear to be poor because the time between last breakdown and December is not calculated into the MTBF.

In the question posted (Bfreese1972) if a running time was available like a projector has a Lamp running time this would a realy usfull field to add to the table. The true uptime would make the MTBF much more accurate. (and easy to calculate)

Garry

 

[Len Boorman]

Doc Man
Excellent explanation as usual

Only comment would be that setting up as you describe is fine for such gurus as yourself but if you are handing over to somebody else to support who is not on such a plane then getting their head around that method could be a touch difficult.

Having said that the saying "If you cannot stand the heat... stay out of the kitchen"
also applies.

We have a lot of cooks rather than chefs here

Len