Summing Split ranges in a SQL query

Question

I have a table which contains my server status

create table ServerStatus
(
    ServerId int, 
    StartTime datetime, 
    Seconds int,
    [State] char(8) 
)

I would like a query that given a start and end date will summarize the time the server spends in each state during that time. I would also like the query to return the amount of time the servers spend in an unknown state.

So, for example for the following data

1   2008-01-01 00:00:00.000 120 broken  
1   2008-01-02 00:00:00.000 120 off     
1   2008-01-03 00:00:00.000 240 burning 
1   2008-01-04 00:00:00.000 60 off     
1   2008-01-05 00:00:00.000 60 off     
2   2008-01-01 00:00:00.000 60 broken  
2   2008-01-02 00:00:00.000 30 off     
2   2008-01-03 00:00:00.000 20 burning 
2   2008-01-04 00:00:00.000 600 off     
3   2007-01-04 00:00:00.000 600 off     
4   2007-12-12 00:00:00.000 999999999 onfire

Provided the range.

select  @start = dateadd(second, 60, '2008-01-01'), 
@fin = dateadd(second, 60, '2008-01-04')

I would like to return the results:

1   broken   60
1   burning  240
1   off      180
1   unknown  258720
2   burning  20
2   off      90
2   unknown  259090
4   onfire   259200

This question is somewhat related to: Combining split date ranges in a SQL query

Answer 1

This is the best I have come up with so far:

declare @start datetime
declare @fin datetime 

select  @start = dateadd(second, 60, '2008-01-01'), @fin = dateadd(second, 60, '2008-01-04')

select ServerId, State, Total = sum(
    case when StartTime > @start and DateAdd(second, Seconds, StartTime) <= @fin
     then Seconds
    when StartTime < @start and DateAdd(second, Seconds, StartTime) <= @fin
     then DateDiff(second, @start, DateAdd(second, Seconds, StartTime)) 
    when StartTime < @start and DateAdd(second, Seconds, StartTime) >= @fin
     then DateDiff(second, @start, @fin)
    else 
     DateDiff(second,StartTime,@fin)
    end)
into #t
from ServerStatus 
where @start < dateadd(second, Seconds, StartTime) 
and @fin > StartTime
group by ServerId, State

insert #t
select ServerId, 'unknown', DateDiff(second, @start, @fin) - sum(Total) 
from #t 
group by ServerId
having DateDiff(second, @start, @fin) > sum(Total)

select * from #t 
order by ServerId, State