我正在尝试找出如何根据我存储在表中的数据来计算各种应用程序的实际停机时间。
目前，我只是在计算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
更新了新的测试案例
这是一种计算唯一中断的技术，然后将其与导致中断的初始停机时间对齐，以使实际值与预期值相匹配。
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
