Database Challenges

--rubeshnee_padayachee_tsqlchallenge_23.sql
--set statistics time on
-- set statistics io off

;WITH Times (RowNum, AuditTime, IsOnline) AS
(
SELECT RowNum = ROW_NUMBER() OVER (ORDER BY t.AuditTime), AuditTime, IsOnline
FROM
(
SELECT TOP 1 AuditTime = CASE (datepart(minute, AuditTime) + datepart(second, AuditTime))
			WHEN 0 THEN (dateadd(hour, datediff(hour, 0, AuditTime)-1,0))
			ELSE (dateadd(hour, datediff(hour, 0, AuditTime),0))
			END
			, IsOnline
FROM dbo.TC23
ORDER BY AuditTime
UNION ALL
SELECT TOP 1 AuditTime =  dateadd(hour, datediff(hour, 0, dateadd(hh, 1, AuditTime)),0)
			, IsOnline
FROM dbo.TC23
ORDER BY AuditTime DESC
UNION ALL
SELECT DATEADD(SECOND, (datediff(second, t1.AuditTime, t2.AuditTime)/2), t1.AuditTime), t2.IsOnline
FROM
(SELECT RN= ROW_NUMBER() OVER (ORDER BY AuditTime), AuditTime, IsOnline FROM dbo.TC23) t1 INNER JOIN
(SELECT RN= ROW_NUMBER() OVER (ORDER BY AuditTime), AuditTime, IsOnline FROM dbo.TC23) t2 ON t1.RN = t2.RN-1
WHERE t1.IsOnline <> t2.IsOnline
) t
)
SELECT TimeFrom = t1.AuditTime
	, TimeTo = t2.AuditTime
	, IsOnline = CASE t1.IsOnline WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END 
FROM Times t1
INNER JOIN Times t2 ON t1.RowNum = t2.RowNum-1
OPTION (MAXDOP 1)

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.