Calculation of the total time outside the dataset in the date / time range

Question

I want to add some new features to an existing application (database - Microsoft SQL 2005). Basically, I'm trying to calculate how many minutes (or seconds) a particular department was “unmanned” for a specific set of date ranges. I am looking for the perfect single statement statement dataset query. I have a regular program that goes through a set of records, analyzes it and gives an answer, but it's pretty ugly. Does anyone have suggestions on how I can optimize it for reading using pure SQL - or even any pointers / articles about what I should look at, my Googlefu is failing.

I think that in some respects it is almost like the “free time” of a calendar search, but aggregated.

The following is an example of a sample dataset to give you an idea of ​​what I'm working with (peer hours are effective, then hours). I use rounding to minutes below for the sake of simplicity, but most likely I will count in a matter of seconds.

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

So, for example, if I requested the above table for department ID = 1, between 04/01/2010 08:30:00 and 04/01/2010 17:30:00, I would expect a result of 35 minutes (or 2100 seconds) “unmanned time” (this is the sum of the time at the beginning, middle, and end of a range that is unmanned).

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

.

Edit:

.

Answer 3

I would recommend using Eric X's approach. Given this disclaimer, this is disgusting, but it offers the means to accomplish the same thing if you do not have access to the number table for one reason or another. I am sure that it can be improved, I just wanted to try it without using a table of numbers:

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

edit added the case statements above to handle the StaffedTime calculation when a certain period starts before @Start (or ends after @End)