Calculating MTBF

[yathinj@gmail.com]

Please help me if anyone have a solution for this
I have a database containing hundreds of records of a machine error logs
and i am creating a project in asp.net to show the performamnce of the equipments.

My Problem:I need to calculate and display the MTBF (mean time between faliures) using SQL statement.
That is calculating the mean time between entries and display the result for each machine.

Database table example:

date time errorcode machinename
19/7/2007 5:33:51 2.1117 CPT01
21/7/2007 4:15:16 2.1267 CPT01
19/7/2007 7:33:51 2.1117 CPT02
21/7/2007 8:15:16 2.1267 CPT02


I need the result to be something like this..

machinename MTBF
CPT01 2:23:45
CPT02 2:23:45

Thanks in advance

 

[MattP]

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.

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

SELECT [Date Time],
	ErrorCode,
	MachineName,
	row_number() OVER (PARTITION BY MachineName ORDER BY [Date Time]) AS rownum
FROM SomeLogTable
ORDER BY MachineName

Result:

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

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:

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.

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:

CTP01	30
CTP01	45
CTP01	15
CTP02	30

Now you just need to find the average for each of these differences.

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:

CTP01	30
CTP02	30

Napkin math shows (30 + 45 + 15) / 3 = 30 and 30 / 1 = 30.

 

[MattP]

Another way to look at it is the difference between the last time and the first time divided by the number of times it's in the file minus 1.

This should get you the same results minus the headache of the first post.

SELECT MachineName, (DATEDIFF(minute, MIN([Date Time]), MAX([Date Time]))) / (COUNT(MachineName) - 1) As MTBF
FROM SomeLogTable
GROUP BY MachineName
ORDER BY MachineName

 

[yathinj@gmail.com]

calcumating MTBF

Hi ,
My Date and Time and in 2 diffrent columns

 

[MattP]

You'll need to concatenate your Date and Time columns and convert them to DateTime to use them.

SELECT MachineName, (DATEDIFF(minute, MIN(DateAndTime), MAX(DateAndTime))) / (COUNT(MachineName) - 1) As MTBF
FROM 
(
	SELECT MachineName, CONVERT(DateTime, [Date] + ' ' + [Time]) AS [DateAndTime] FROM SomeLogTable
) AS dt
GROUP BY MachineName
ORDER BY MachineName

If you're going to be working with DateTime values consistantly I'd recommend making a view.

CREATE VIEW UsefulDateTime AS
SELECT MachineName, ErrorCode, CONVERT(DateTime, [Date] + ' ' + [Time]) AS [DateAndTime]
FROM SomeLogTable

This way you can simplify future queries

SELECT *
FROM UsefulDateTime

Result:

CTP01	2.1117	2009-01-14 05:30:00.000
CTP01	2.1267	2009-01-14 06:00:00.000
CTP01	2.1117	2009-01-14 06:45:00.000
CTP02	2.1117	2009-01-14 06:50:00.000
CTP01	2.1267	2009-01-14 07:00:00.000
CTP02	2.1267	2009-01-14 07:20:00.000

Quick check to make sure we're getting the same data from the view.

SELECT MachineName, (DATEDIFF(minute, MIN(DateAndTime), MAX(DateAndTime))) / (COUNT(MachineName) - 1) As MTBF
FROM UsefulDateTime
GROUP BY MachineName
ORDER BY MachineName

Result:

CTP01	30
CTP02	30

 

[yathinj@gmail.com]

calculating MTBF

Wow.. awsome ..
Thanks dude , Its working now . Your second post was simple and effective . I just changed my database table to handle datetime in one single column because i keep getting this error "The data types date and varchar are incompatible in the add operator" when using CONVERT(DateTime, [Date] + ' ' + [Time]).
Anyway its working now . The result is what i needed.
Thanks for all the help .
Regards
Yathin

Greetings from singapore

 

[MattP]

Good to hear that it's working for you.

I kept the 1st post there because it is sometimes useful to have a running count by group.

It sounds like you've got SQL Server 2008 if you've got a Date type available to you. I hate how + means concatenate and add depending on context. To get it to work in your original setup you'd need to have an inner Covert to Varchar on your [Date] column. In your case it's much better to have a DateTime column to begin with.

 

[cjard]

In Oracle, you have LAG and LEAD analytic functions which allow access of rows around the current row. If SQLServer has this, it could also have been used rather than joining to rownum=rownum-1

 

[MattP]

I would love to be able to use LAG and LEAD.

The 2nd solution works better for the original poster. I left the 1st post in to show how to get a running count by group and how I go about tackling a complex solution.

Feedback Thread Let Microsoft know you want LAG/LEAD in future releases of SQL Server.

 

[cjard]

I would love to be able to use LAG and LEAD.

You should use a proper database then; lag and lead have been in Oracle for at least 10 years..

I like some things about SQLServer, like.. er.. it works. And it integrates well with VS, but I'd happily forgo these minor perks because everything else about it on a basic level is plain retarded. Take the way ISNULL works; it sounds like it returns a boolean, but it's actually a crippled and stupid form of COALESCE. Another great example is date handling; if your date isnt in one of the 17 predefined formats that microsoft wants you to use, youre stuffed. Oracle has had methods that work like .NET's ParseExact for more than 10 years..

Mmm.. so, for me SQLServer "has the dumb" - I probably won't vote for LAG/LEAD because I'll just use Oracle