Database Challenges

--erik_eckhardt_tsqlchallenge_23_v3.sql
WITH Plex AS (
	SELECT
		AuditTime,
		IsOnline = Convert(tinyint, IsOnline),
		Pos = Row_Number() OVER (ORDER BY AuditTime)
	FROM
		TC23
		CROSS JOIN (SELECT 0 UNION ALL SELECT 1) X (N)
), DePlex AS (
	SELECT TOP 1 WITH TIES
		AuditTime = DateAdd(ss, DateDiff(ss, Min(AuditTime) OVER (PARTITION BY Pos / 2), Max(AuditTime) OVER (PARTITION BY Pos / 2)) / 2, Min(AuditTime) OVER (PARTITION BY Pos / 2)),
		IsOnline,
		Pos
	FROM Plex
	ORDER BY
		CASE WHEN Max(IsOnline) OVER (PARTITION BY Pos / 2) <> Min(IsOnline) OVER (PARTITION BY Pos / 2)
			OR Count(*) OVER (PARTITION BY Pos / 2) = 1 THEN 0 ELSE 1 END
), Parts AS (
	SELECT
		AuditTime =
			CASE
			WHEN Pos = 1 THEN DateAdd(hh, DateDiff(hh, '20000101', DateAdd(ms, -3, AuditTime)), '20000101')
			WHEN Row_Number() OVER (ORDER BY Pos DESC) = 1 THEN DateAdd(hh, 1 + DateDiff(hh, '20000101', AuditTime), '20000101')
			ELSE AuditTime
			END,
		IsOnline,
		Part = (Row_Number() OVER (ORDER BY Pos) + 1) / 2
	FROM DePlex
)
SELECT
	TimeFrom = Min(AuditTime),
	TimeTo = Max(AuditTime),
	IsOnline = CASE IsOnline WHEN 1 THEN 'Yes' ELSE 'No' END
FROM Parts
GROUP BY IsOnline, Part
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.