How to find The Turnaround Time (TAT) in Oracle SQL

The Turnaround Time (TAT) in Oracle SQL is typically calculated as:

TAT = END_TIME - START_TIME

It measures total time taken from initiation to completion of a task — not just the repair time.

---

🧾 Assumed Table Structure

CREATE TABLE TASKS (
    TASK_ID        NUMBER,
    START_TIME     DATE,
    END_TIME       DATE,
    TASK_TYPE      VARCHAR2(50)
);

---

✅ Basic TAT Query (in Hours)

SELECT 
    TASK_ID,
    ROUND((END_TIME - START_TIME) * 24, 2) AS TAT_HOURS
FROM 
    TASKS
WHERE 
    START_TIME IS NOT NULL 
    AND END_TIME IS NOT NULL;

---

🕒 TAT in Days

SELECT 
    TASK_ID,
    ROUND((END_TIME - START_TIME), 2) AS TAT_DAYS
FROM 
    TASKS
WHERE 
    START_TIME IS NOT NULL 
    AND END_TIME IS NOT NULL;

---

📊 Average TAT

Average TAT in Hours:

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

---

📆 TAT by Task Type or Month (Optional)

TAT by Task Type:

SELECT 
    TASK_TYPE,
    ROUND(AVG((END_TIME - START_TIME) * 24), 2) AS AVG_TAT_HOURS
FROM 
    TASKS
WHERE 
    START_TIME IS NOT NULL AND END_TIME IS NOT NULL
GROUP BY 
    TASK_TYPE;

TAT by Month:

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