Database Challenges

--parth_patel_tsqlchallenge_23_v1.sql
;WITH Base AS
(
	SELECT AuditTime, IsOnline, 
	Grp = ROW_NUMBER() OVER (ORDER BY AuditTime) + ROW_NUMBER() OVER (PARTITION BY IsOnline ORDER BY AuditTime DESC)
	FROM TC23
),
BaseGrp AS
(
	SELECT TimeFrom = MIN(AuditTime), TimeTo = MAX(AuditTime), IsOnline
	FROM Base
	GROUP BY Grp, IsOnline
),
BaseGrpNo AS
(
	SELECT TimeFrom, TimeTo, IsOnline = CASE IsOnline WHEN 1 THEN 'Yes' ELSE 'No' END, 
	GrpNo = ROW_NUMBER() OVER (ORDER BY TimeFrom)
	FROM BaseGrp
)
SELECT 
TimeFrom =
CASE 
	WHEN B2.TimeTo IS NULL THEN 
		DATEADD (
				HOUR, 
				DATEPART(HOUR, B1.TimeFrom) - CASE WHEN SIGN(DATEPART(MINUTE, B1.TimeFrom) + DATEPART(SECOND, B1.TimeFrom)) = 1 THEN 0 ELSE 1 END, 
				DATEADD(DAY, 0, DATEDIFF(DAY, 0, B1.TimeFrom))
				)
	ELSE
		DATEADD(SECOND, FLOOR(DATEDIFF(SECOND, B2.TimeTo, B1.TimeFrom)/2), B2.TimeTo)
END,
TimeTo =
CASE 
	WHEN B3.TimeFrom IS NULL THEN 
		DATEADD(HOUR, DATEPART(HOUR, B1.TimeTo) + 1, DATEADD(DAY, 0, DATEDIFF(DAY, 0, B1.TimeTo)))
	ELSE
		DATEADD(SECOND, FLOOR(DATEDIFF(SECOND, B1.TimeTo, B3.TimeFrom)/2), B1.TimeTo)
END,
B1.IsOnline
FROM BaseGrpNo B1 
LEFT OUTER JOIN BaseGrpNo B2 ON B1.GrpNo = B2.GrpNo + 1
LEFT OUTER JOIN BaseGrpNo B3 ON B1.GrpNo = B3.GrpNo - 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.