SQL Server Performance Forum – Threads Archive
Query to find idle time from a tableHi 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
There are a few solution for that problem. Just to show the basic idea:
(select 0 as [hour]
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. —
Microsoft SQL Server MVP
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.