SQL Function for averaged time elapsed between variables when it's over 24 hours

user_12520768 0
Viewed 80

I have a table that logs status codes. I'm trying to calculate MTTR (Mean time to repair) by running code that logs time elapsed from when the component fails to when it is back online. We also need to ignore incidents where a component fails but is repaired in under 24 hours, as these are typically training exercises. Finally, I want to average the time elapsed so I have a constantly updating MTTR for each location.

Here's how my data looks, with "2000" representing a failure code and "1000" being normal operation:

An example of the data, with "2000" representing a failure code and "1000" being normal operation

with that example, I would want to count the failure highlighted in red, but ignore the one in yellow. And I'm doing this for millions of records over time, and averaging the elapsed time by location.

I've tried using the datediff() function in conjunction with some other methods, but have not been successful for my end result. Can someone help me with the proper SQL syntax to achieve my desired result? I use the Microsoft SQL server management studio.

sql-server sql
1 Answers

This is a type of gaps-and-islands problem. You can assign the islands using a difference of row numbers. Subject to your conditions:

select min(datetime), max(datetime),
       datediff(second, min(datetime), max(datetime)) / (24.0 * 60 * 60) as decimal_days
from (select t.*,
             row_number() over (partition by statuscode order by datetime) as seqnum_2,
             row_number() over (order by datetime) as seqnum
      from t
     ) t
where statuscode = 2000
group by (seqnum - seqnum_2)
having max(datetime) > dateadd(day, 1, min(datetime))

If you want the average of these, use a subquery.

Note: I would count the time-to-repair as the time to the next status code 1000, not the last 2000. This would affect the query. If this is what you really want, then ask a new question.

user_11440350
Related