Database Challenges

--tomas_boixet_tsqlchallenge_23.sql
-- tomas_boixet_tsqlchallenge_23
;WITH withrow AS (
	SELECT *, ROW_NUMBER() OVER (ORDER BY AuditTime) AS rownum
	FROM ( SELECT DISTINCT AuditTime, IsOnline FROM TC23 ) a
)
,startend AS (
	SELECT DATEADD(second, DATEPART(second, qstart)*-1, DATEADD(minute, DATEPART(minute, qstart)*-1, qstart)) AS tstart
		, DATEADD(second, DATEPART(second, qend)*-1, DATEADD(minute, DATEPART(minute, qend)*-1, qend)) AS tend
		, rstart, rend
	FROM (
		SELECT DATEADD(second, -1, MIN(AuditTime)) AS qstart, 
			DATEADD(hour, 1, MAX(AuditTime)) AS qend, MIN(rownum) AS rstart, MAX(rownum) AS rend
		FROM withrow
	) a
)
, rgroups AS (
	SELECT rownum, tstart AS TimeFrom, 
		CASE WHEN rownum=rend THEN tend ELSE AuditTime END AS TimeTo, IsOnline, 1 AS rgroup
	FROM withrow
	CROSS JOIN startend
	WHERE rownum=rstart
	UNION ALL
	SELECT w.rownum, CASE WHEN r.IsOnline=w.IsOnline THEN TimeFrom ELSE AuditTime END AS TimeFrom,
		CASE WHEN w.rownum=rend THEN tend ELSE w.AuditTime END AS TimeTo, w.IsOnline,
		CASE WHEN r.IsOnline=w.IsOnline THEN rgroup ELSE 1+rgroup END AS rgroup
	FROM withrow w
	INNER JOIN rgroups r ON w.rownum=r.rownum+1
	CROSS JOIN startend 
)
, blocks AS (
	SELECT MIN(rownum) AS fromrow, MAX(rownum) AS torow, 
		MIN(TimeFrom) AS TimeFrom, MAX(TimeTo) AS TimeTo, 
		IsOnline, rgroup
	FROM rgroups
	GROUP BY rgroup, IsOnline
)
SELECT CASE WHEN c.fromrow=rstart THEN c.TimeFrom ELSE
		DATEADD(s, ROUND((DATEDIFF(s, p.TimeTo, c.TimeFrom)+0.5)/2, 0)*-1, c.TimeFrom) END AS TimeFrom,
	CASE WHEN c.torow=rend THEN c.TimeTo ELSE
		DATEADD(s, ROUND((DATEDIFF(s, c.TimeTo, n.TimeFrom)-0.5)/2, 0), c.TimeTo) END AS TimeTo,
	CASE WHEN c.IsOnline=1 THEN 'Yes' ELSE 'No' END AS IsOnline
FROM blocks c
CROSS JOIN startend
LEFT JOIN blocks p ON p.rgroup=c.rgroup-1
LEFT JOIN blocks n ON n.rgroup=c.rgroup+1
ORDER BY c.rgroup

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.