Calculate Actual Downtime ignoring overlap in dates/times

Question

I'm trying to work out how to calculate actual downtime for various applications from data I'm storing within a table.

At the moment I'm just calculating the difference between DowntimeStart and DowntimeEnd which is shown in the DowntimeMinutes.

The problem is that if there is a cross-over in times as separate components are down, it should count the total ignoring the over-lap.

What I expect is shown in the Expected column.

Any ideas on how a query could be put together to achieve this?

Application         DowntimeStart           DowntimeEnd              DowntimeMinutes    Expected
Application Demo    2014-11-20 17:31:01.467 2014-11-20 18:01:01.243  30                 30
Application Demo    2014-11-28 17:59:00.987 2014-11-28 18:09:02.167  10                 26
Application Demo    2014-11-28 18:00:01.403 2014-11-28 18:25:01.443  25                 0
Application Demo    2014-11-29 19:13:08.580 2014-11-30 05:30:01.763  617                617
Application Demo    2014-11-30 01:55:01.953 2014-11-30 03:54:01.730  119                0

I've taken a look and investigated these options but they don't achieve the above:

Find Total Minutes Ignoring Overlap (Convert Cursor based Answer to CTE)

SQL to find time elapsed from multiple overlapping intervals

http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28169653.html

http://thehobt.blogspot.com.au/2009/04/calculating-elapsed-time-based-upon.html

https://forums.teradata.com/forum/database/duration-calculation-between-dates-which-has-overlap-with-other-date-ranges-for-same

Jason W · Accepted Answer · 2014-12-24T18:10:07.193

UPDATED WITH NEW TEST CASES

Here is one technique that calculates the unique outages and then aligns them back to the initial downtime causing the outage so that the actual and expected values match.

DECLARE @Downtime TABLE (
    ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    Application VARCHAR(25), 
    DowntimeStart DATETIME,
    DowntimeEnd DATETIME,
    Expected INT
)

INSERT @Downtime (Application, DowntimeStart, DowntimeEnd, Expected) VALUES -- Act/Exp
    ('Application Demo', '2014-11-20 17:31:01.467', '2014-11-20 18:01:01.243', 30) -- 30/30
    ,('Application Demo', '2014-11-28 17:59:00.987', '2014-11-28 18:09:02.167', 26) -- 10/26
    ,('Application Demo', '2014-11-28 18:00:01.403', '2014-11-28 18:25:01.443', 0) -- 25/0
    ,('Application Demo', '2014-11-29 19:13:08.580', '2014-11-30 05:30:01.763', 617) -- 617/617
    ,('Application Demo', '2014-11-30 01:55:01.953', '2014-11-30 03:54:01.730', 0)
    ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 3514)
    ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0)
    ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) 
    ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0)
    ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) 

SELECT
    Downtimes.Application,
    Downtimes.DowntimeStart,
    Downtimes.DowntimeEnd,
    Downtimes.Expected,
    COALESCE(Actual, 0) AS Actual
FROM @Downtime Downtimes
    LEFT OUTER JOIN (
        SELECT DISTINCT
            D1.Application,
            MIN(CASE WHEN D1.DowntimeStart < D2.DowntimeStart THEN D1.ID ELSE D2.ID END) AS [ID],
            MIN(CASE WHEN D1.DowntimeStart < D2.DowntimeStart THEN D1.DowntimeStart ELSE D2.DowntimeStart END) AS [DowntimeStart],
            MAX(CASE WHEN D1.DowntimeEnd > D2.DowntimeEnd THEN D1.DowntimeEnd ELSE D2.DowntimeEnd END) AS [DowntimeEnd],
            DATEDIFF(MINUTE,
                MIN(CASE WHEN D1.DowntimeStart < D2.DowntimeStart THEN D1.DowntimeStart ELSE D2.DowntimeStart END),
                MAX(CASE WHEN D1.DowntimeEnd > D2.DowntimeEnd THEN D1.DowntimeEnd ELSE D2.DowntimeEnd END)) AS Actual
        FROM @Downtime D1
            INNER JOIN @Downtime D2
                ON D1.Application = D2.Application
                    AND (D1.DowntimeStart BETWEEN D2.DowntimeStart AND D2.DowntimeEnd
                        OR D2.DowntimeStart BETWEEN D1.DowntimeStart AND D1.DowntimeEnd)
        GROUP BY
            D1.Application,
            D1.DowntimeStart
    ) Outages
        ON Outages.ID = Downtimes.ID

And this yields the desired output:

Application               DowntimeStart           DowntimeEnd             Expected    Actual
------------------------- ----------------------- ----------------------- ----------- -----------
Application Demo          2014-11-20 17:31:01.467 2014-11-20 18:01:01.243 30          30
Application Demo          2014-11-28 17:59:00.987 2014-11-28 18:09:02.167 26          26
Application Demo          2014-11-28 18:00:01.403 2014-11-28 18:25:01.443 0           0
Application Demo          2014-11-29 19:13:08.580 2014-11-30 05:30:01.763 617         617
Application Demo          2014-11-30 01:55:01.953 2014-11-30 03:54:01.730 0           0
Application Demo 2        2014-12-19 23:09:01.303 2014-12-22 09:43:01.397 3514        3514
Application Demo 2        2014-12-19 23:09:01.303 2014-12-22 09:43:01.397 0           0
Application Demo 2        2014-12-19 23:09:01.303 2014-12-22 09:43:01.397 0           0
Application Demo 2        2014-12-19 23:09:01.303 2014-12-22 09:43:01.397 0           0
Application Demo 2        2014-12-19 23:09:01.303 2014-12-22 09:43:01.397 0           0

Minor data validation issue here: The last row's start time is between the previous row's start and end times, and thus should have affected both durations. — Jaaz Cole, Dec 23 '14 at 15:32
Thanks very much Jason, there's a problem I didn't consider though until I tested with all data. If you have the following, see next message, it returns the same value for all. Anyway to modify the query to take this into account? — Philip, Dec 23 '14 at 21:03
INSERT #TABLENAME (Application, DowntimeStart, DowntimeEnd, Expected) VALUES - ('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 3514) ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) - ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) — Philip, Dec 23 '14 at 21:04
Does your table have a primary key that can be used for the change? — Jason W, Dec 23 '14 at 23:50
Yes it does. Is just straight named "ID" and is of type INT. — Philip, Dec 24 '14 at 00:23
Thanks very much Jason, much appreciated. Apologies for the late reply too, I was away over the Xmas/NY period. One final point if you don't mind? I've changed your query slightly to get a summary of the totals. Downtimes.Application, sum(Actual) AS Actual In doing this though, I need to get a total of 0 for those applications that don't have a record within the Downtime table. How could that be done using a lookup? i.e. Thanks INSERT Applications (Application) VALUES ('Application Demo') ,('Application Demo 2') ,('Application Demo 3') ,('Application Demo 4') — Philip, Jan 05 '15 at 00:13
If you have an Applications table, then LEFT OUTER JOIN Applications to your downtime table, and then COALESCE or ISNULL the downtime value with 0 if the value is NULL (ie: `COALESCE(DATEDIFF(...), 0)`) — Jason W, Jan 08 '15 at 03:01

score 0 · Answer 2 · edited May 23 '17 at 12:20

I had a similar problem, and I got it answered in my question How to Consolidate Blocks of Time?

In your case, this is accomplished with a top 1 self outer apply to get the overlap, and then use either the Overlap's Start time, or if null, the normal Record's end time as the end time.

CREATE TABLE Downtime (
    Application VARCHAR(25), 
    DowntimeStart DATETIME,
    DowntimeEnd DATETIME,
    Expected INT
)

INSERT Downtime (Application, DowntimeStart, DowntimeEnd, Expected) VALUES -- Act/Exp
    ('Application Demo', '2014-11-20 17:31:01.467', '2014-11-20 18:01:01.243', 30) -- 30/30
    ,('Application Demo', '2014-11-28 17:59:00.987', '2014-11-28 18:09:02.167', 26) -- 10/26
    ,('Application Demo', '2014-11-28 18:00:01.403', '2014-11-28 18:25:01.443', 0) -- 25/0
    ,('Application Demo', '2014-11-29 19:13:08.580', '2014-11-30 05:30:01.763', 617) -- 617/617
    ,('Application Demo', '2014-11-30 01:55:01.953', '2014-11-30 03:54:01.730', 0)


SELECT
    Records.Application, Records.DowntimeStart, Records.DowntimeEnd, Records.Expected
  , DATEDIFF(minute, Records.DowntimeStart, COALESCE(Overlap.DowntimeStart, Records.DowntimeEnd)) AS Actual
--  , Overlap.Application, Overlap.DowntimeStart, Overlap.DowntimeEnd -- For Verification Purposes
FROM Downtime Records
  OUTER APPLY (
    SELECT TOP 1 Overlap.Application, Overlap.DowntimeStart, Overlap.DowntimeEnd
    FROM Downtime Overlap
    WHERE Records.Application = Overlap.Application
      AND Overlap.DowntimeStart > Records.DowntimeStart
      AND Overlap.DowntimeStart BETWEEN Records.DowntimeStart AND Records.DowntimeEnd
    ORDER BY Overlap.DowntimeStart
    ) Overlap

Here's the SQLFiddle with the solution.

Thanks very much Jaaz, there's a problem I didn't consider though until I tested with all data. If you have the following, see next message, it returns the same value for all. Anyway to modify the query to take this into account? — Philip, Dec 23 '14 at 21:04
INSERT #TABLENAME (Application, DowntimeStart, DowntimeEnd, Expected) VALUES - ('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 3514) ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) - ,('Application Demo 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397', 0) — Philip, Dec 23 '14 at 21:05

Calculate Actual Downtime ignoring overlap in dates/times

2 Answers2

Linked