Database Challenges

--Dalibor_Margotic_tsqlchallenge_23.sql
;WITH CTEResult AS (
SELECT ROW_NUMBER() OVER(ORDER BY AuditTime) AS RBR, * 
FROM TC23
)
, CTEResult2 AS 
(
	SELECT 
		ROW_NUMBER() OVER(ORDER BY COALESCE(CTE1.RBR, CTE2.rbr)) AS RBR
	,
		CASE WHEN CTE2.RBR IS NULL THEN 
			DATEADD(HOUR,DATEPART(HOUR,DATEADD(SECOND,-1,CTE1.AuditTime)),DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(SECOND,-1,CTE1.AuditTime)),0))
		WHEN CTE1.RBR IS NULL THEN 
			DATEADD(MINUTE,DATEPART(MINUTE,60-DATEADD(SECOND,1,CTE2.AuditTime)),DATEADD(SECOND, DATEPART(SECOND,60-DATEADD(SECOND,1,CTE2.AuditTime)), DATEADD(SECOND,1,CTE2.AuditTime)))
		ELSE 
			DATEADD(SECOND,(DATEDIFF(SECOND,CTE1.AuditTime, CTE2.AuditTime)-1) / 2,CTE1.AuditTime)
		END 
		AS Time
	,	CTE1.IsOnline
	FROM CTEResult AS CTE1
	FULL MERGE JOIN CTEResult AS CTE2 ON
		CTE1.RBR = CTE2.RBR + 1
	WHERE NOT CTE1.IsOnline = CTE2.IsOnline OR CTE2.IsOnline IS NULL OR CTE1.rbr = 1 OR CTE1.IsOnline is null
) 
SELECT 
	CASE WHEN CTE2.Time IS NOT NULL
	THEN CTE2.Time
	ELSE
		DATEADD(HOUR,DATEPART(HOUR,DATEADD(millisecond,-3,CTE1.Time)),DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(millisecond,-3,CTE1.Time)),0))
	END
	AS TimeFrom
,	CTE1.Time AS TimeTo 
,	CASE CTE2.IsOnline WHEN 1 THEN 'Yes' ELSE 'No' END AS IsOnline
FROM CTEResult2 AS CTE1
INNER MERGE JOIN CTEResult2 AS CTE2 ON
	CTE1.RBR = CTE2.RBR + 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.