You'll need to concatenate your Date and Time columns and convert them to DateTime to use them.
Code:
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.
Code:
CREATE VIEW UsefulDateTime AS
SELECT MachineName, ErrorCode, CONVERT(DateTime, [Date] + ' ' + [Time]) AS [DateAndTime]
FROM SomeLogTable
This way you can simplify future queries
Code:
SELECT *
FROM UsefulDateTime
Result:
Code:
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.
Code:
SELECT MachineName, (DATEDIFF(minute, MIN(DateAndTime), MAX(DateAndTime))) / (COUNT(MachineName) - 1) As MTBF
FROM UsefulDateTime
GROUP BY MachineName
ORDER BY MachineName
Result: