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

2022-01-16 00:00:00 sql sql-server sql-server-2008-r2

我正在尝试根据我存储在表中的数据计算各种应用程序的实际停机时间.

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

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

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:

查找忽略重叠的总分钟数(转换基于光标的 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

推荐答案

更新了新的测试用例

这是一种计算唯一中断然后将它们与导致中断的初始停机时间对齐的技术，以便实际值和预期值匹配.

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

这会产生所需的输出:

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

相关文章