sql - 事件的总持续时间、间隔中可能存在的间隙和重叠

Question

有一个时间间隔列表。总是有开始和结束日期时间，例如:

TimeFrom                  TimeTill
2014-07-10 07:00:00.000   2014-07-10 11:30:00.000
2014-07-10 13:00:00.000   2014-07-10 14:00:00.000
2014-07-10 13:00:00.000   2014-07-10 14:30:00.000

TimeFrom 和TimeTill 总是在同一天，没有异常(exception)。让我们假设这代表花在不同项目上的时间(在本例中是三个)。允许重叠。

我想请求有关 SQL Server 2008 R2 查询的帮助，以获取此人白天工作的总时间。

请注意，有一段时间没有工作。在本例中，它介于 11:30 和 13:00 之间。

当我使用 min(TimeFrom) 和 max(TimeTill) 来计算最早开始和最晚结束之间的持续时间时，它对重叠部分工作得很好，除了我无法减去不工作的间隔。

期望的输出是这样的:

Day         TimeWorking
2014-07-10  360

非常感谢您的帮助!

保罗

Answer 1

您可以为一整天构建一个 1 分钟间隔的列表。 exists 查询可以过滤掉没有工作的分钟数。结果计数是完成工作的分钟数:

; with  all_minutes as
        (
        select  cast('00:00' as time) as time
        union all
        select  dateadd(minute, 1, time)
        from    all_minutes
        where   time < cast('23:59' as time)
        )
select  cast(yt.TimeFrom as date) as day
,       count(distinct am.time) as minutes_worked
from    YourTable yt
left join
        all_minutes am
on      cast(yt.TimeFrom as time) <= am.time
        and am.time < cast(yt.TimeTo as time)
group by
        cast(TimeFrom as date)
option  (maxrecursion 0)
;

Example at SQL Fiddle.