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
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.