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.

score 0 · Answer 1 · answered Oct 05 '18 at 10:47

0

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.

answered Oct 05 '18 at 10:47

mhd.cs

711
2
10
28

It is possible to create a dates and time interval table as suggested here http://www.kodyaz.com/t-sql/create-date-time-intervals-table-in-sql-server.aspx but this solution seems to be primitive – Eralper Oct 05 '18 at 14:50

score 0 · Answer 2 · answered Oct 05 '18 at 11:07

0

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.

answered Oct 05 '18 at 11:07

Radim Bača

10,646
1
19
33

@Eralper could you send the dbfiddle demo with this data? thanks – Radim Bača Oct 05 '18 at 12:11
Sorry when I execute on DBFiddler, I see it validated my case – Eralper Oct 05 '18 at 13:03

score 0 · Answer 3 · answered Oct 09 '18 at 05:57

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,

The code works, but is it possible to make without the table in the beginning? I have to put it into Tableau, which does not accept queries with tables. — Malene Fangel Høgsvig, Oct 15 '18 at 12:54
Do you mean to exclude the CTE expression? Does it accept subselects? In fact the CTE expression is a Recursive one. So it is not possible to omit recursive CTE and implement an other solution. — Eralper, Oct 15 '18 at 13:09
You can create a SQL View with above code. Then for tableau you can reference to this additional view — Eralper, Oct 15 '18 at 13:12

Calculate downtime

3 Answers3