Finding “dead time” in a database of start and end times.

The following snippet will find “dead time” (e.g. time where no events are scheduled) in a database:

    1 select distinct dateadd(s,-1,starttime) as deadtime,"start" from sometable t where
    2  0=(select count(*) from sometable u where u.starttime < t.deadtime and u.endtime > t.deadtime)
    3 union all
    4 select distinct dateadd(s,1,endtime) as deadtime,"end" from sometable t where
    5  0=(select count(*) from sometable u where u.starttime < t.deadtime and u.endtime > t.deadtime)
    6 order by deadtime