我需要计算学期的总和。重叠术语不应计数多次。
Declare @Terms table (Start DATETIME, Finish DATETIME)
INSERT INTO @Terms values
('2016-8-3 08:00','2016-8-3 09:00'),
('2016-8-3 09:00','2016-8-3 10:00'),
('2016-8-3 08:00','2016-8-3 09:30'), -- overlapping term
('2016-8-3 11:00','2016-8-3 12:00')
上面数据的结果应为180分钟（3小时）。最简单，最快的方法是什么？
在十字架的帮助下申请。
Declare @Terms table (Start DATETIME, Finish DATETIME)
INSERT INTO @Terms values
('2016-8-3 08:00','2016-8-3 09:00'),
('2016-8-3 09:00','2016-8-3 10:00'),
('2016-8-3 08:00','2016-8-3 09:30'), -- overlapping term
('2016-8-3 11:00','2016-8-3 12:00')
Select Minutes = sum(Minutes)
From (
Select Distinct
B.DateR1
,B.DateR2
,Minutes = DateDiff(Minute,B.DateR1,B.DateR2)
From @Terms A
Cross Apply (
Select DateR1=Min(Start)
,DateR2=max(Finish)
From @Terms
Where Start <= A.Finish and Finish >= A.Start
) B
) A
退货
Minutes
180
子查询返回
DateR1 DateR2 Minutes
2016-08-03 08:00:00.000 2016-08-03 10:00:00.000 120
2016-08-03 11:00:00.000 2016-08-03 12:00:00.000 60
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句