First cut, may have some issues, but I'll keep working on it.
Works for the given data, just need to try additional scenarios
declare @timeranges table ( StartDateTime datetime, EndDateTime datetime ) declare @blockedtimes table ( StartDateTime datetime, EndDateTime datetime ) insert into @timeranges select '01 Jan 2009 09:00:00', '01 Jan 2009 17:00:00' union select '02 Feb 2009 10:00:00', '02 Feb 2009 13:00:00' --union select '03 Feb 2009 10:00:00', '03 Feb 2009 15:00:00' insert into @blockedtimes select '01 Jan 2009 13:00:00', '01 Jan 2009 13:30:00' union select '02 Feb 2009 10:30:00', '02 Feb 2009 11:00:00' union select '02 Feb 2009 12:00:00', '02 Feb 2009 12:30:00' --build an ordered, time range table with an indicator --to determine which ranges are timeranges 'tr' --and which are blockedtimes 'bt' -- declare @alltimes table (row int, rangetype varchar(10), StartDateTime datetime, EndDateTime datetime ) insert into @alltimes select row_number() over (order by a.startdatetime), * from ( select 'tr' as rangetype ,startdatetime, enddatetime from @timeranges union select 'bt' as rangetype ,startdatetime, enddatetime from @blockedtimes )a --what does the data look like -- select * from @alltimes -- -- build up the results select --start time is either the start time of a timerange, or the end of a blockedtime case when at1.rangetype = 'tr' then at1.startdatetime when at1.rangetype = 'bt' then at1.enddatetime end as [Start], case --a time range followed by another time range : end time from the current time range when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'tr' then at1.enddatetime --a time range followed by nothing (last record) : end time from the currenttime range when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) is null then at1.enddatetime --a time range followed by a blockedtime : end time is start time of blocked time when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'bt' then (select top 1 at2.startdatetime from @alltimes at2 where at2.row > at1.row and at2.rangetype = 'bt' order by row) --a blocked time followed by a blockedtime : end time is start time of next blocked time when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'bt' then (select top 1 at2.startdatetime from @alltimes at2 where at2.row > at1.row and at2.rangetype = 'bt' order by row) --a blocked time followed by a time range : end time is end time of previous time range when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'tr' then (select top 1 at2.enddatetime from @alltimes at2 where at2.row < at1.row and at2.rangetype = 'tr' order by row desc) --a blocked time followed by nothing (last record) : end time is end time of previous time range when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) is null then (select top 1 at2.enddatetime from @alltimes at2 where at2.row < at1.row and at2.rangetype = 'tr' order by row desc) end as [End] from @alltimes at1