Query to find idle time from a table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query to find idle time from a table

Hi All, I have a table with fields ID (identity), Start(Start Time) End(End Time) and data as below. ID Start End
———– ———– ———–
2 10 11
1 10 12
3 10 14
6 11 12
4 13 16
5 18 20 What I want to get from the table is idle time. Here I know one hour is idle that is 17. I tried many methods but didn’t work out. Thanks
Aris

There are a few solution for that problem. Just to show the basic idea:
select h.[hour]
from
(select 0 as [hour]
union
select 1
union
select 2
union

select 22
union
select 23) as h
where not exists(select *
from table t
where t.startTime <= h.[hour] and h.[hour] < t.endTime)

Check out Richard Snodgrass’ homepagehttp://www.cs.arizona.edu/people/rts/ You can download a pdf version of his brilliant "Developing Time-Oriented Database Applications in SQL" book along with all sourcecode there for free. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Thank you very much. I will try the TSQL and I will reffer the book too
You can create a table variable and loads all hour partition data as they are going to be same for all day from 1 to 24.
Then join it with your table and check for null condition.
By this you can get all the missing hours.
]]>