从选择查询的开始时间和结束时间列表中，我需要找出不包括重叠时间和休息时间的总时间。
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 分钟。
我不想使用这么多循环来完成这个任务。寻找一些简单的解决方案。
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
