quote:

---

Originally posted by rokhri

second row is also not convered by t1 from 11:01 to 13:00

---

Declare @t1 Table (
	tStart datetime, 
	tEnd datetime
)

INSERT INTO @t1
SELECT '2011/03/01 08:01','2011/03/01 11:00'
UNION ALL
SELECT '2011/03/01 13:01','2011/03/01 16:00'
UNION ALL
SELECT '2011/03/01 18:01','2011/03/01 23:59'

Declare @t2 Table (
	tStart datetime, 
	tEnd datetime
)
/*
INSERT INTO @t2
SELECT '2011/03/01 06:00', '2011/03/01 11:00'  
UNION ALL
SELECT '2011/03/01 19:00',' 2011/03/01 22:00'
UNION ALL
SELECT '2011/03/01 10:00','2011/03/01 13:30'  -- Overlaps with 6-11 record??
*/
INSERT INTO @t2
SELECT '2011/03/01 06:00', '2011/03/01 10:00'  
UNION ALL
SELECT '2011/03/01 19:00',' 2011/03/01 22:00'
UNION ALL
SELECT '2011/03/01 10:00','2011/03/01 13:30'  


;with z 
As (
	Select 
		dy = dateadd(dy,datediff(dy,0,tStart),0),
		tStart,
		tEnd,
		r = Row_Number() Over(Partition By dateadd(dy,datediff(dy,0,tStart),0) Order By tStart)
	From @t1 A
), y 
As (
	Select 
		*,
		r = Row_Number() Over(Partition By dateadd(dy,datediff(dy,0,tStart),0) Order By tStart)
	From
		(
		Select 
			dy = isnull(A.dy,B.dy),
			tStart = isnull(A.tStart,isnull(A.dy,B.dy)),
			tEnd = isnull(A.tEnd,B.tStart),
			InT1 = case when A.tStart is not null then 1 else 0 end
		From z A
		Full Join z B
		On A.r = B.r-1
		
		Union All
		
		Select 
			dy = A.dy,
			tStart = A.tEnd,
			tEnd = isnull(B.tStart,dateadd(minute,-1,A.dy+1)),
			InT1 = 0
		From z A
		Left Join z B
		On A.r = B.r-1
		) C
	Where tStart <> tEnd
), x
As (
	Select 
		dy = dateadd(dy,datediff(dy,0,tStart),0),
		tStart,
		tEnd,
		r = Row_Number() Over(Partition By dateadd(dy,datediff(dy,0,tStart),0) Order By tStart)
	From @t2 A
), w 
As (
	Select 
		*,
		r = Row_Number() Over(Partition By dateadd(dy,datediff(dy,0,tStart),0) Order By tStart)
	From
		(
		Select 
			dy = isnull(A.dy,B.dy),
			tStart = isnull(A.tStart,isnull(A.dy,B.dy)),
			tEnd = isnull(A.tEnd,B.tStart),
			InT2 = case when A.tStart is not null then 1 else 0 end
		From x A
		Full Join x B
		On A.r = B.r-1
		
		Union All
		
		Select 
			dy = A.dy,
			tStart = A.tEnd,
			tEnd = isnull(B.tStart,dateadd(minute,-1,A.dy+1)),
			InT2 = 0
		From x A
		Left Join x B
		On A.r = B.r-1
		) C
	Where tStart <> tEnd
), cte
As (
	Select 
		a.dy,
		--C.n,
		tStart = case C.n 
					when 1 then 
						case when A.tStart < B.tStart then A.tStart else B.tStart end
					when 2 then 
						case when A.tStart < B.tStart then B.tStart else A.tStart end
					when 3 then 
						case when A.tEnd < B.tEnd then A.tEnd else B.tEnd end
				end,
		tEnd = 
			case C.n
					when 1 then 
						case when A.tStart < B.tStart then B.tStart else A.tStart end
					when 2 then 
						case when A.tEnd < B.tEnd then A.tEnd else B.tEnd end
					when 3 then
						case when A.tEnd < B.tEnd then B.tEnd else A.tEnd end
			end,
		InT1 = case C.n 
					when 1 then 
						case when A.tStart < B.tStart then A.InT1 else 0 end
					when 2 then A.InT1
					when 3 then 
						case when A.tStart < B.tStart then 0 else A.InT1 end
				end,
		InT2 = case C.n 
					when 1 then 
						case when A.tStart < B.tStart then 0 else B.InT2 end
					when 2 then B.InT2
					when 3 then 
						case when A.tStart < B.tStart then B.InT2 else 0 end
				end,
				
		tStart1 = A.tStart,
		tEnd1 = A.tEnd,
		T1 = A.InT1,
		tStart2 = B.tStart,
		tEnd2 = B.tEnd,
		T2 = B.InT2,
		r = Row_Number() Over (Partition By A.dy Order By A.r, B.r, C.n)
	From y A
	Left Join w B
	On A.tStart <= B.tEnd
	and A.tEnd > B.tStart
	Cross Join (Select n=1 Union Select n=2 Union Select n=3) C
)

Select 
	dy,
	tStart,
	tEnd,
	InT1 = max(InT1),
	InT2 = max(InT2)
From cte 
Where tStart <> tEnd
group by dy, tStart, tEnd
Having max(InT2) = 0
Order By 1, 2

quote:

---

Originally posted by nigelrivett

maybe

;with cte21 as (select startdate = MIN(TStartDate), enddate = MAX(TEndDate) from #Table2)
, cte22 as
(select tme = startdate from cte21
union all
select DATEADD(mi,1,tme) from cte22 where tme < (select enddate from cte21)
)
,cte23 as (select * from cte22 where exists (select * from #Table2 where tme between TStartDate and TEndDate))
,cte as (select tme from cte23 where not exists (select * from #Table1 where tme between StartDate and EndDate)) -- gaps
,ctea as (select *, seq = ROW_NUMBER() over (order by tme) from cte c1 where not exists (select * from cte c2 where DATEADD(mi,1,c2.tme) = c1.tme))
,cteb as (select *, seq = ROW_NUMBER() over (order by tme) from cte c1 where not exists (select * from cte c2 where DATEADD(mi,1,c1.tme) = c2.tme))
select startdate = ctea.tme, enddate = cteb.tme from ctea
join cteb on ctea.seq = cteb.seq
option (maxrecursion 0)


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by rokhri

nigelrivett
i need ur permission to publish this solution through my blog

---

quote:

---

Originally posted by nigelrivett

Here's a version with a few coments
...

---