How to find MTTR (Mean Time To Repair) in Oracle

In Oracle SQL, to calculate MTTR (Mean Time To Repair), you typically need a table that records fault start time and fault end time for each incident.

---

🧾 Assumptions: Table Structure

Assume a table like:

CREATE TABLE FAULTS (
    FAULT_ID       NUMBER,
    EQUIPMENT_ID   VARCHAR2(50),
    START_TIME     DATE,
    END_TIME       DATE
);

Each row represents a fault incident, with:

1. START_TIME: when the fault occurred
2. END_TIME: when the fault was resolved

---

✅ MTTR Formula

MTTR is the average repair time per fault, i.e.:

MTTR = AVG(END_TIME - START_TIME)

---

🔍 Oracle SQL Query for MTTR in Hours

SELECT 
    ROUND(AVG((END_TIME - START_TIME) * 24), 2) AS MTTR_HOURS
FROM 
    FAULTS
WHERE 
    START_TIME IS NOT NULL AND END_TIME IS NOT NULL;

🔧 Explanation:

1. (END_TIME - START_TIME) gives difference in days
2. Multiply by 24 to convert to hours
3. ROUND(..., 2) rounds the result to 2 decimal places

---

🕒 Optional: MTTR by Equipment or Month

MTTR by Equipment:

SELECT 
    EQUIPMENT_ID,
    ROUND(AVG((END_TIME - START_TIME) * 24), 2) AS MTTR_HOURS
FROM 
    FAULTS
WHERE 
    START_TIME IS NOT NULL AND END_TIME IS NOT NULL
GROUP BY 
    EQUIPMENT_ID;

MTTR by Month:

SELECT 
    TO_CHAR(START_TIME, 'YYYY-MM') AS FAULT_MONTH,
    ROUND(AVG((END_TIME - START_TIME) * 24), 2) AS MTTR_HOURS
FROM 
    FAULTS
WHERE 
    START_TIME IS NOT NULL AND END_TIME IS NOT NULL
GROUP BY 
    TO_CHAR(START_TIME, 'YYYY-MM')
ORDER BY 
    FAULT_MONTH;