Calculate downtime

Question

I would like to calculate the downtime for some processes.

My data could look like this:

Proces    ID     StartTime             EndTime  
A         1      24-07-2018 00:00:00   24-07-2018 00:02:54
A         2      24-07-2018 00:00:16   24-07-2018 00:02:55
A         3      24-07-2018 11:12:42   24-07-2018 11:15:10
A         4      24-07-2018 00:00:16   24-07-2018 00:02:55

In this example, ID 1, 2 and 4 are overlapping, but the downtime should only be from 00.00.00 to 00.02.55 plus the downtime for ID 3.

I am not sure how to compare all the times and only getting it to use the overlapping time once.

If it is unclear, then ask!

I hope someone can help me.

Answer 1

i think is better that handle this business Out of TSQL , For example in your Application you can get each day and use a bitarray for each minute and calculate minimum and maximum Time in each overlapping range . this is very complex in tsql and i thinks every solution has Performance ISSUE.

Answer 2

Could be solved using self-join as follows

select t.process, sum(datediff(second, t.StartTime, t.EndTime))
from
(
  select distinct d1.process, min(d2.StartTime) StartTime, max(d2.EndTime) EndTime
  from data d1
  left join data d2 on d2.EndTime > d1.StartTime and d2.StartTime < d1.EndTime
  group by d1.process, d1.id
) t
group by t.process

DBFiddle DEMO

However, the performance for large data can be quite poor. At least indexes on (process, id, endtime) and (process, id, starttime) should be available.

Answer 3

Could you please try following SQL query with more data

Please try to create sample data for different processes as well

This query sums downtime grouped by process, you can remove process from aggregation SELECT statement (which is the last query) to calculate overall downtime. Or even add GroupId to the list for downtimes per chains of overlapping downtime periods

Please have a look at SQL tutorial on SQL Queries for Overlapping Time Periods which explains the solution in detail

;with rawdata as (
    select
        Process, id, StartTime, EndTime,
        ROW_NUMBER() over (partition by Process order by StartTime, EndTime) as rn
    from Processes
), cte as (
    select
        Process, StartTime, EndTime, rn, 1 as GroupId
    from rawdata
    where rn = 1

    union all

    select
        p1.Process,
        case 
        when (p1.starttime between p2.starttime and p2.endtime) then p2.starttime
        when (p2.starttime between p1.starttime and p1.endtime) then p1.starttime
        when (p1.starttime < p2.starttime and p1.endtime > p2.endtime) then p1.starttime
        when (p1.starttime > p2.starttime and p1.endtime < p2.endtime) then p2.starttime
        else p2.starttime
        end as StartTime, 

        case 
        when (p1.EndTime between p2.starttime and p2.endtime) then p2.EndTime
        when (p2.endtime between p1.starttime and p1.endtime) then p1.endtime
        when (p1.starttime < p2.starttime and p1.endtime > p2.endtime) then p1.endtime
        when (p1.starttime > p2.starttime and p1.endtime < p2.endtime) then p2.endtime
        else p2.endtime
        end as EndTime, 

        p2.rn,
        case when
            (p1.starttime between p2.starttime and p2.endtime) or
            (p1.endtime between p2.starttime and p2.endtime) or
            (p1.starttime < p2.starttime and p1.endtime > p2.endtime) or
            (p1.starttime > p2.starttime and p1.endtime < p2.endtime) 
        then
            p1.GroupId
        else 
            (p1.GroupId+1)
        end as GroupId
    from cte p1
    inner join rawdata p2
        on p1.Process = p2.Process and
           (p1.rn+1) = p2.rn
)
select 
    Process,
    sum(datediff(second, StartTime, EndTime)) totalDownTime
from (
    select
        Process, GroupId, min(StartTime) StartTime, max(EndTime) EndTime
    from cte
    group by Process, GroupId
) t
group by Process

Output is as follows

Hoping to be useful,