在日期/时间范围内计算数据集之外的总时间

Question

我正在为现有应用程序添加一些新功能（数据库是Microsoft SQL 2005）。基本上，我试图计算特定部门在特定日期范围内“无人”的分钟数（或秒数）。我希望用一个语句理想地查询数据集。我有一个循环遍历记录集的例程，解析它并吐出一个答案，但它非常难看。有没有人有任何建议，我可以优化它的可读性，使用纯SQL - 甚至任何指针/我应该看的文章，我的Googlefu是让我失望。

我想在某些方面，这几乎就像一个日历的“空闲时间”搜索，但聚合。

这里是一个模拟样本数据集，让您了解我正在处理的内容（有效地同事进入时钟，然后计时）。为了简单起见，我使用的是四舍五入到几分钟，但我可能会在几秒钟内计算。

------------------------------------------------------------------------ 
| Colleague Id | Department Id | Date In   | Date Out   | 
------------------------------------------------------------------------ 
| 1   | 1    | 04/01/2010 08:45 | 04/01/2010 11:45 | 
| 2   | 1    | 04/01/2010 09:00 | 04/01/2010 12:15 | 
| 3   | 1    | 04/01/2010 10:00 | 04/01/2010 12:00 | 
| 4   | 1    | 04/01/2010 12:30 | 04/01/2010 17:00 | 
| 1   | 1    | 04/01/2010 12:45 | 04/01/2010 17:15 | 
| 3   | 1    | 04/01/2010 13:00 | 04/01/2010 17:25 | 
| 5   | 2    | ...    | ...    | 
------------------------------------------------------------------------ 

因此，例如，如果我查询上表中为部门ID = 1，04/01/2010 8时三十分00秒和04/01/2010 17点30分○○秒之间，我期望的结果是35分钟（或2100秒）“无人驾驶时间”（这是无人驾驶的范围的开始，中间和结束时间的总和）。

Answer 1

我有一个表已经创建的整数，我用这样的东西。

鉴于这种情况，你想：

drop table foo 
go 

create table foo (
    c_id int not null, 
    d_id int not null, 
    datein datetime not null, 
    dateout datetime not null 
) 
go 


insert into foo values (1, 1, '04/01/2010 08:45', '04/01/2010 11:45') 
insert into foo values (2, 1, '04/01/2010 09:00', '04/01/2010 12:15') 
insert into foo values (3, 1, '04/01/2010 10:00', '04/01/2010 12:00') 
insert into foo values (4, 1, '04/01/2010 12:30', '04/01/2010 17:00') 
insert into foo values (1, 1, '04/01/2010 12:45', '04/01/2010 17:15') 
insert into foo values (3, 1, '04/01/2010 13:00', '04/01/2010 17:25') 
go 


drop procedure unmanned 
go 

create procedure unmanned 
    @d_id int, 
    @start datetime, 
    @end datetime 

as 

select distinct dateadd(ss,i_int,@start) 
from Integers 
     left join foo on dateadd(ss,i_int,@start) >= datein and dateadd(ss,i_int,@start) < dateout 


where i_int between 0 and 60*60*24 
and dateadd(ss,i_int,@start) >= @start and dateadd(ss,i_int,@start)< @end 
and datein is null 
order by 1 

go 

exec unmanned 1, '4/1/10 8:30', '4/1/10 17:30' 

Answer 2

这是一个范围交集的问题：您现在看到的一些范围：

4/01/2010 08:30:00 - 04/01/2010 17:30:00 

这个范围可以表示为数字 - 微秒，或从当天开始秒，例如：

[1000000, 3000000] 

，你想找到它不与任何碰撞的部位：

[1200000, 1250000] 
[1250000, 1490000] 
[1500000, 1950000] 
... 

当转换为数字格式时，它可以用几乎任何语言实现。

编辑：

有大约日期范围很大图示和说明here一个很有趣的讨论。

Answer 3

我推荐使用埃里克·H公司的做法。有了这个免责声明，这是有点讨厌，但它确实提供了一种做同样的事情的手段，如果你由于某种原因没有访问数字表。我敢肯定，这可以提高，我只是觉得想出来的W/O使用数字表：

Declare @Start DateTime, @End DateTime 

Select @Start = '04/01/2010 09:30' 
    , @End = '04/01/2010 17:30' 

--Table Creation Stuff 
Declare @y Table (ColleagueId Int, DepartmentId Int, DateIn DateTime, DateOut DateTime) 

Insert @y 
Select 1, 1, '04/01/2010 08:45' , '04/01/2010 11:45' 
Union All Select 2 , 1, '04/01/2010 09:00' , '04/01/2010 12:15' 
Union All Select 3 , 1, '04/01/2010 10:00' , '04/01/2010 12:00' 
Union All Select 4 , 1, '04/01/2010 12:30' , '04/01/2010 17:00' 
Union All Select 1 , 1, '04/01/2010 12:45' , '04/01/2010 17:15' 
Union All Select 3 , 1, '04/01/2010 13:00' , '04/01/2010 17:25' 
--------- 

Select DateDiff(minute, @Start, @End) -- TotalTime 
    - Sum(DateDiff(minute, 
     Case When DateIn < @Start Then @Start Else DateIn End, 
     Case When DateOut > @End Then @End Else DateOut End)) --StaffedTime 
    as UnmannedTime 
From 
(
    Select Min(din) DateIn, dout DateOut 
    From 
    (
     Select Min(y.DateIn) din, Max(y2.DateOut) dout 
     From @y y 
     Inner Join @y y2 on y.DateOut >= y2.DateIn 
     --you probably want to close the other end of these filters, but leave some room 
     --(to handle the guy who started @ 7:45, etc...) 
     Where y.DateIn < @End 
      and y2.DateOut > @Start    
     Group By y.DateIn 
    ) x 
    Group By dout 
) q 

编辑加入上面的case语句来处理StaffedTime的计算，当某一特定时期开始之前到@Start（或结束@End）