Edit: better solution in my next post.
Hope you've got SQL2005+. I'll break this down to how I solved it so maybe somebody can learn how to tackle a problem like this in the future.
Here's the dummy table I set up to test values.
Code:
2009-01-14 05:30:00.000 2.1117 CTP01
2009-01-14 06:00:00.000 2.1267 CTP01
2009-01-14 06:45:00.000 2.1117 CTP01
2009-01-14 06:50:00.000 2.1117 CTP02
2009-01-14 07:00:00.000 2.1267 CTP01
2009-01-14 07:20:00.000 2.1267 CTP02
Awesome, so no running count of records for each machine
Code:
SELECT [Date Time],
ErrorCode,
MachineName,
row_number() OVER (PARTITION BY MachineName ORDER BY [Date Time]) AS rownum
FROM SomeLogTable
ORDER BY MachineName
Result:
Code:
2009-01-14 05:30:00.000 2.1117 CTP01 1
2009-01-14 06:00:00.000 2.1267 CTP01 2
2009-01-14 06:45:00.000 2.1117 CTP01 3
2009-01-14 07:00:00.000 2.1267 CTP01 4
2009-01-14 06:50:00.000 2.1117 CTP02 1
2009-01-14 07:20:00.000 2.1267 CTP02 2
Now that I've got a rownum for each record I can compare it to the time with a rownum that's offset by 1. In the example below I'm doing an INNER JOIN on MachineName and rownum = rownum - 1
Code:
SELECT *
FROM (SELECT [Date Time],
ErrorCode,
MachineName,
row_number() OVER (PARTITION BY MachineName ORDER BY [Date Time]) AS rownum
FROM SomeLogTable) As prev
INNER JOIN (
SELECT [Date Time],
ErrorCode,
MachineName,
row_number() OVER (PARTITION BY MachineName ORDER BY [Date Time]) AS rownum
FROM SomeLogTable) As next
ON prev.rownum = next.rownum - 1 AND prev.MachineName = next.MachineName
Result:
Code:
2009-01-14 05:30:00.000 2.1117 CTP01 1 2009-01-14 06:00:00.000 2.1267 CTP01 2
2009-01-14 06:00:00.000 2.1267 CTP01 2 2009-01-14 06:45:00.000 2.1117 CTP01 3
2009-01-14 06:45:00.000 2.1117 CTP01 3 2009-01-14 07:00:00.000 2.1267 CTP01 4
2009-01-14 06:50:00.000 2.1117 CTP02 1 2009-01-14 07:20:00.000 2.1267 CTP02 2
Getting the difference in minutes between each entry.
Code:
SELECT prev.MachineName, DATEDIFF(minute, prev.[Date Time], next.[Date Time]) AS MTBF
FROM (SELECT [Date Time],
ErrorCode,
MachineName,
row_number() OVER (PARTITION BY MachineName ORDER BY [Date Time]) AS rownum
FROM SomeLogTable) As prev
INNER JOIN (
SELECT [Date Time],
ErrorCode,
MachineName,
row_number() OVER (PARTITION BY MachineName ORDER BY [Date Time]) AS rownum
FROM SomeLogTable) As next
ON prev.rownum = next.rownum - 1 AND prev.MachineName = next.MachineName
Result:
Code:
CTP01 30
CTP01 45
CTP01 15
CTP02 30
Now you just need to find the average for each of these differences.
Code:
SELECT prev.MachineName, AVG(DATEDIFF(minute, prev.[Date Time], next.[Date Time])) AS MTBF
FROM (SELECT [Date Time],
ErrorCode,
MachineName,
row_number() OVER (PARTITION BY MachineName ORDER BY [Date Time]) AS rownum
FROM SomeLogTable) As prev
INNER JOIN (
SELECT [Date Time],
ErrorCode,
MachineName,
row_number() OVER (PARTITION BY MachineName ORDER BY [Date Time]) AS rownum
FROM SomeLogTable) As next
ON prev.rownum = next.rownum - 1 AND prev.MachineName = next.MachineName
GROUP BY prev.MachineName
Result:
Napkin math shows (30 + 45 + 15) / 3 = 30 and 30 / 1 = 30.