计算总时间（不包括重叠时间和SQLServer中的中断）

Question

从选择查询的开始时间和结束时间列表中，我需要找出不包括重叠时间和休息时间的总时间。

StartTime                   EndTime
2014-10-01 10:30:00.000     2014-10-01 12:00:00.000 -- 90 mins
2014-10-01 10:40:00.000     2014-10-01 12:00:00.000 --0 since its overlapped with     previous
2014-10-01 10:42:00.000     2014-10-01 12:20:00.000 -- 20 mins excluding overlapped time
2014-10-01 10:40:00.000     2014-10-01 13:00:00.000 -- 40 mins
2014-10-01 10:44:00.000     2014-10-01 12:21:00.000 -- 0 previous ones have already covered this time range
2014-10-13 15:50:00.000     2014-10-13 16:00:00.000 -- 10 mins

因此，在这种情况下，总计应为160分钟。

我不想使用太多循环来解决这个问题。寻找一些简单的解决方案。

Answer 1

DECLARE @table TABLE (StartTime DateTime2, EndTime DateTime2)

INSERT INTO @table SELECT '2014-10-01 10:30:00.000', '2014-10-01 12:00:00.000'
INSERT INTO @table SELECT '2014-10-01 10:40:00.000', '2014-10-01 12:00:00.000'
INSERT INTO @table SELECT '2014-10-01 10:42:00.000', '2014-10-01 12:20:00.000'
INSERT INTO @table SELECT '2014-10-01 10:40:00.000', '2014-10-01 13:00:00.000'
INSERT INTO @table SELECT '2014-10-01 10:44:00.000', '2014-10-01 12:21:00.000'
INSERT INTO @table SELECT '2014-10-13 15:50:00.000', '2014-10-13 16:00:00.000'


;WITH addNR AS ( -- Add row numbers

    SELECT StartTime, EndTime, ROW_NUMBER() OVER (ORDER BY StartTime, EndTime) AS RowID
    FROM @table AS T

), createNewTable AS ( -- Recreate table according overlap time

    SELECT StartTime, EndTime, RowID 
    FROM addNR
    WHERE RowID = 1

    UNION ALL

    SELECT 
        CASE 
            WHEN a.StartTime <= AN.StartTime AND AN.StartTime <= a.EndTime THEN a.StartTime 
            ELSE AN.StartTime END AS StartTime, 
        CASE WHEN a.StartTime <= AN.EndTime AND AN.EndTime <= a.EndTime THEN a.EndTime 
            ELSE AN.EndTime END AS EndTime,
        AN.RowID 
    FROM addNR AS AN
    INNER JOIN createNewTable AS a
        ON a.RowID + 1 = AN.RowID

), getMinutes AS ( -- Get difference in minutes
    SELECT DATEDIFF(MINUTE,StartTime,MAX(EndTime)) AS diffMinutes
    FROM createNewTable
    GROUP BY StartTime
)
SELECT SUM(diffMinutes) AS Result
FROM getMinutes

结果是160