Calculate MTBF with SQL query

Question

I'm trying to calculate the expected average time between the beginning of the failure and the beginning of the next (same column) [MTBF].

I already found a similar question here but it didn't help me.

I have to calculate the difference between the dates in the Failure column ((n+1) - n) for each line(other column), transforming it into hours and dividing it by the number of intervals to calculate the average time.

This is my table:

|       Failure        |    Start_Repair      |    End_Of_Repair     | Line  | Piece  |
|----------------------|----------------------|----------------------|-------|--------|
| 2019-06-26 06:30:00  | 2019-06-26 10:40:00  | 2019-06-27 12:00:00  | A     | tube   |
| 2019-06-28 00:10:00  | 2019-06-28 02:40:00  | 2019-06-29 01:12:00  | A     | washer |
| 2019-06-30 10:10:00  | 2019-06-30 02:40:00  | 2019-07-01 00:37:00  | B     | bolt   |
| 2019-07-02 12:01:00  | 2019-07-02 14:24:00  | 2019-07-05 00:35:00  | B     | engine |

So I have to make the difference on the failure column, second minus first, third minus second, etc. All divided by the calculated intervals (which are the number of lines-1 since I start from line 2-line 1).

This is the sql code I wrote, it doesn't work ...

SELECT ROW_NUMBER() over (ORDER BY t1.Line, t1.Failure ASC) AS 'Row',
(DATEDIFF(HOUR, T1.failure, T2.failure))/'Row' AS '[MTBF]'
From Test_Failure as t1, Test_Failure as t2
where t1.Failure < t2.Failure

Error: Conversion failed when converting the varchar value 'Row' to data type int.

The result should come back:

A = (41.6 + 198.96)/2 = 120 h

B = (49.85 + 116.35)/2 = 83,1h

score 0 · Accepted Answer · answered Jun 26 '19 at 14:35

0

If I understand correctly, you can do this with aggregation:

select line,
       datediff(hour, min(failure), max(failure)) / nullif(count(*) - 1, 0)
from test_failure f
group by line;

That is, the average time between failures is the latest failure minus the earliest divided by one less than the count.

answered Jun 26 '19 at 14:35

Gordon Linoff

1,242,037
58
646
786

It is almost correct ... In the example there are 2 lines for A and 2 lines for B, so it would be good to do max - min, but in the moment that they are 5 lines per line I should do the subtraction of every single line. I should start from the last one and go to the first one getting the hours of difference (5 row-4 row, 4 row-3 row, 3 row -2 row, 2 row-1 row), add them and divide them by one less than the count . – AlexMagic Jun 26 '19 at 14:46
@AlexMagic . . . Nope. This works in all cases. you will observe that ((row3 - row2) + (row2 - row1)) equals (row3 - row1) by the rules of arithmetic. – Gordon Linoff Jun 26 '19 at 14:55

score 0 · Answer 2 · answered Jun 26 '19 at 15:12

Conversion failed when converting the varchar value 'Row' to data type int.

You got this error because you put single quotes around your first column alias and then tried to use it in the calculation of your second column. You can't do that.

This line:

(DATEDIFF(HOUR, T1.failure, T2.failure))/'Row' AS '[MTBF]'

is trying to divide the DATEDIFF result (an integer) by the word "Row" (a string). You can't divide a number by a word.

For what it's worth, even if you didn't use the single quotes, you still can't use the alias of a column in another column's calculation.

Calculate MTBF with SQL query

2 Answers2