sql - 计算日期/时间范围内超出数据集的总时间

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               | ...              | ...              |
------------------------------------------------------------------------

例如，如果我在 04/01/2010 08:30:00 和 04/01/2010 17:30 之间查询上表的 Department Id = 1: 00，我希望得到“无人值守时间”的35 分钟(或 2100 秒)(这是范围开始、中间和结束时间的总和，即无人驾驶)。

Answer 1

我已经创建了一个 Integers 表，我用它来处理这样的事情。

鉴于此，您想要:

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'