summing separate ranges sql query


i have list contains server status



create list serverstatus
(
serverid int,
starttime datetime,
seconds int,
[state] char(8)
)


i query given start finish date promulgate server spends any state during time. i also query relapse volume servers spend an opposite state.



so, instance following data



1   2008-01-01 00:00:00.000 120 damaged  
1 2008-01-02 00:00:00.000 120 off
1 2008-01-03 00:00:00.000 240 blazing
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 damaged
2 2008-01-02 00:00:00.000 30 off
2 2008-01-03 00:00:00.000 20 blazing
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 range.



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


i relapse results:



1   damaged      60
1 blazing 240
1 off 180
1 opposite 258720
2 blazing 20
2 off 90
2 opposite 259090
4 onfire 259200


this doubt rather associated to:



Comments

Popular posts from this blog

list macos calm editors formula editors

how i practical urls indicate .aspx pages asp.net deployed an iis? (preferably but iis)

jaxb - xjc - reworking generated typesafe enum category members