如何使用重叠的开始和结束时间来总结15分钟间隔内发生的活动时间（SQL）（t-SQL）

假设有一些合理的SQL Server最新版本，这应该是一个好的开始：

-- Some sample data.
declare @Samples as Table ( SampleId Int Identity, Start DateTime, Stop DateTime );
insert into @Samples ( Start, Stop ) values
  ( '2/2/2015 7:00', '2/2/2015 7:25' ),
  ( '2/2/2015 7:20', '2/2/2015 7:29' ),
  ( '2/2/2015 7:35', '2/2/2015 7:42' ),
  ( '2/2/2015 8:05', '2/2/2015 8:14' ),
  ( '2/2/2015 8:16', '2/2/2015 8:20' ),
  ( '2/2/2015 8:29', '2/2/2015 8:40' ),
  ( '2/2/2015 8:55', '2/2/2015 9:25' );
select * from @Samples;

-- Find the limits and align them to quarter hours.
declare @Min as DateTime;
declare @Max as DateTime;
select @Min = min( Start ), @Max = max( Stop )
  from @Samples;
set @Min = DateAdd( minute, -DatePart( minute, @Min ) % 15, @Min );
set @Max = DateAdd( minute, 15 - DatePart( minute, @Max ) % 15, @Max );
select @Min as [Min], @Max as [Max];


-- Go for it.
with QuarterHours ( QuarterStart, QuarterStop )
  as (
    select @Min, DateAdd( minute, 15, @Min )
    union all
    select QuarterStop, DateAdd( minute, 15, QuarterStop )
      from QuarterHours
      where QuarterStop < @Max ),
  Overlaps
  as ( select QH.QuarterStart, QH.QuarterStop, S.Start, S.Stop,
  case
    when S.Start <= QH.QuarterStart and S.Stop >= QH.QuarterStop then 15
    when S.Start <= QH.QuarterStart and S.Stop < QH.QuarterStop then DateDiff( minute, QH.QuarterStart, S.Stop )
    when S.Start > QH.QuarterStart and S.Stop >= QH.QuarterStop then DateDiff( minute, S.Start, QH.QuarterStop )
    when S.Start > QH.QuarterStart and S.Stop < QH.QuarterStop then DateDiff( minute, S.Start, S.Stop )
    else 0 end as Overlap
  from QuarterHours as QH left outer join
    @Samples as S on S.Start <= QH.QuarterStop and S.Stop >= QH.QuarterStart )
  select QuarterStart, sum( Overlap ) as [ActivityTime]
    from Overlaps
    group by QuarterStart
    order by QuarterStart;

您可以将最后一个更改select为select * from QuarterHours或select * from Overlaps以查看一些中间值。

解释性说明：

您可以使用所需的任何范围（@Min/ @Max），我只是从示例数据中获取了它们，以便示例可以运行。出于相同的原因，我使用了一个表变量，出于示例的缘故，无需创建“真实”表。

公用表表达式（CTE）通过递归创建QuarterHours覆盖所需范围的表。（A号码表或帐簿表也可以被用于产生四分之一小时）。然后，LEFT OUTER JOIN与所述采样数据被用于定位所有的Overlaps，如果有的话，每个季度小时。这样可以保留没有活动的四分之一小时。

最后SELECT总结了结果。