How to calculate Mean Time Between Failure (MTBF) using SQL

Ning Xu 0
Viewed 24

Given Issue Raised day and count, how can we come up with the calculation of Datediff, and the output table with MTBF for each month? Thanks a lot.

Raw data

Issue Raised | Count | Datediff  
1/12/22         1      12
2/23/22         1      42
4/1/22          2      37
4/7/22          1      6

Output table

Month | MTBF
Jan     12/1=12
Feb     42/1=42
Mar     
Apr     (37+6)/(2+1)=14.33
sql
1 Answers

As mentioned you could use a group by function to aggregate by month, year and then do your calculations. Here is something that might work for you, however I am uncertain of your DBMS.

  SELECT 
 FORMAT(ISSUERAISED, 'MM-yyyy') AS Month_Year
      ,SUM("Count") AS "Count"
      ,SUM("DateDiff") AS "DATEDIFF"
      , SUM(CAST("DateDiff" AS DECIMAL(7,4))) / SUM(CAST("Count" AS DECIMAL(7,4))) AS MTBF
  FROM YOURTABLE

  GROUP BY  FORMAT(ISSUERAISED, 'MM-yyyy')
Vlookup0
Related