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 ResultFROM getMinutes
结果是160
