Database Challenges

--Mueller_Lutz_tsqlchallenge_23.sql
-- submitted by Lutz Mueller on 02/14/2010
-- version 1

;WITH AuditTimeNumbered AS
(
	SELECT  ROW_NUMBER() OVER(ORDER BY AuditTime) AS ROW, AuditTime, IsOnline
	FROM    TC23
)
,
AuditTimeRelevant AS
(
	SELECT  
		ATNmb1.IsOnline,  
		ROW_NUMBER() OVER(ORDER BY ISNULL(ATNmb1.AuditTime, ATNmb2.AuditTime)) rown,
		CASE 
			WHEN ATNmb2.AuditTime IS NULL THEN DATEADD(hh,DATEDIFF(hh,0,DATEADD(ms,-10,ATNmb1.AuditTime)),0) 
			WHEN ATNmb1.AuditTime IS NULL THEN DATEADD(hh,DATEDIFF(hh,0,DATEADD(ms,10,ATNmb2.AuditTime))+1,0)
			ELSE DATEADD(ss,DATEDIFF(ss,ATNmb2.AuditTime,ATNmb1.AuditTime)/2,ISNULL(ATNmb2.AuditTime,ATNmb1.AuditTime)) 
			END AS t
	FROM AuditTimeNumbered ATNmb1
	FULL OUTER JOIN AuditTimeNumbered ATNmb2
		ON ATNmb1.row = ATNmb2.row + 1
	WHERE ISNULL(ATNmb1.IsOnline ^ ATNmb2.IsOnline,1)= 1
)
SELECT 
	ATR1.t AS TimeFrom,
	ATR2.t AS TimeTo,
	CASE WHEN ATR1.IsOnline = 1 THEN 'Yes' 
		ELSE 'No' END IsOnline
FROM    AuditTimeRelevant ATR1
	INNER JOIN AuditTimeRelevant ATR2
	ON ATR1.rown = ATR2.rown - 1
ORDER BY TimeFrom

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.