Database Challenges

--Michael_Lewis_tsqlchallenge_23.sql
WITH AddRIDs AS (
SELECT	 AuditTime
		,IsOnline
		,ROW_NUMBER() OVER(ORDER BY AuditTime) AS Seq
FROM TC23
)
,GenerateTimeDiffs AS (
SELECT	 AddRIDs.IsOnline
		,PrevState.IsOnline AS PrevIsOnline
		,DATEADD(ss, FLOOR(DATEDIFF(ss, PrevState.AuditTime, AddRIDs.AuditTime) / 2.00000), PrevState.AuditTime) AS TimeDiff
		,ROW_NUMBER() OVER(ORDER BY AddRIDs.AuditTime) AS Seq
FROM AddRIDs
 INNER JOIN AddRIDs AS PrevState
   ON AddRIDs.Seq = PrevState.Seq + 1
  AND AddRIDs.IsOnline = ~PrevState.IsOnline
)
SELECT	 ISNULL(GenerateTimeDiffs.TimeDiff, MinMax.MinTime) AS TimeFrom
		,ISNULL(PrevDiff.TimeDiff, MinMax.MaxTime) AS TimeTo
		,CASE WHEN ISNULL(GenerateTimeDiffs.IsOnline, PrevDiff.PrevIsOnline) = 1 THEN 'Yes' ELSE 'No' END AS IsOnline
FROM GenerateTimeDiffs
 FULL OUTER JOIN GenerateTimeDiffs AS PrevDiff
   ON GenerateTimeDiffs.Seq = PrevDiff.Seq - 1
 FULL OUTER JOIN
	(SELECT	 MIN(DATEADD(mi, -(DATEPART(mi, DATEADD(ss, -1, AuditTime))), DATEADD(ss, -(DATEPART(ss, DATEADD(ss, -1, AuditTime))), DATEADD(ss, -1, AuditTime)))) AS MinTime
			,MAX(DATEADD(hh, 1, DATEADD(mi, -(DATEPART(mi, AuditTime)), DATEADD(ss, -(DATEPART(ss, AuditTime)), AuditTime)))) AS MaxTime
	 FROM TC23) AS MinMax
   ON 1 = 1		-- having this as a full outer join, rather than cross join, allows a single input to return a result set (and is quicker)
ORDER BY ISNULL(GenerateTimeDiffs.TimeDiff, MinMax.MinTime)
		,ISNULL(PrevDiff.TimeDiff, MinMax.MaxTime)

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.