计算实际停机时间而忽略日期/时间的重叠

Question

我正在尝试找出如何根据我存储在表中的数据来计算各种应用程序的实际停机时间。

目前，我只是在计算DowntimeMinutes中显示的DowntimeStart和DowntimeEnd之间的差异。

问题是，如果由于单独的组件故障而导致时间跨度，则应计入总数，而忽略重叠。

我的期望显示在“期望”列中。

关于如何将查询组合在一起以实现此目标的任何想法？

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

我看了看并研究了这些选项，但它们没有达到上述目的：

查找忽略重叠的总分钟数（将基于光标的答案转换为CTE）

SQL查找多个重叠间隔中经过的时间

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

Answer 1

更新了新的测试案例

这是一种计算唯一中断的技术，然后将其与导致中断的初始停机时间对齐，以使实际值与预期值相匹配。

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

这将产生所需的输出：

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