quote:

---

Originally posted by pequeajim

I'm thinking of doing this with a couple of table value functions that move the raw data into a usable table that I can easily report against?

One TVF would provide the extracted data for Mean Time Between Failure, (1=Run, then first 0=Down), and the second would give me the extracted data for Mean Time To Recover, (0=Down, then 1=Run)

The report would kick off the procedure which would include running a cursor function that extract out the data so it can be reported on.

I may not be using the right terminology for everything, but think the concept should be solid. I need to go back and study up on how to use this functionality in SQL Server.

---

SELECT t.machinecenter,t.machine,
SUM(CASE WHEN t.status=0 and t1.status=1 THEN 1 ELSE 0 END) AS downcount,
SUM(CASE WHEN t.status=0 and t1.status=1 THEN DATEDIFF(ss,t.date,t1.date) ELSE 0 END) AS totaldowntime,
SUM(CASE WHEN t.status=1 and t1.status=0 THEN DATEDIFF(ss,t.date,t1.date) ELSE 0 END) AS totaltimeavailable
FROM YourTable t 
CROSS APPLY (SELECT TOP 1 date
             FROM YourTable
             WHERE machinecenter=t.machinecenter
             AND machine= t.machine
             AND status <> t.status
             AND date > t.date
             ORDER BY date ASC)t1
GROUP BY t.machinecenter,t.machine