Solution to TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log By Beakdan

WITH Seq AS (
	SELECT	ROW_NUMBER() OVER(ORDER BY dat.AuditTime) AS rn,
			dat.AuditTime, dat.IsOnline
	FROM	dbo.TC23 AS dat
),
Periods AS (
	SELECT	ROW_NUMBER() OVER(ORDER BY p.TimeMark) AS rn, p.TimeMark, p.IsOnline
	FROM	(
				SELECT	CASE
							WHEN a.AuditTime IS NULL THEN
								DATEADD([HOUR], CEILING(DATEDIFF([MINUTE], '19000101', b.AuditTime) / 60.0) - 1, '19000101')
							WHEN b.AuditTime IS NULL THEN 
								DATEADD([HOUR], FLOOR(DATEDIFF([MINUTE], '19000101', a.AuditTime) / 60.0) + 1, '19000101')
							ELSE DATEADD([SECOND], DATEDIFF([SECOND], a.AuditTime, b.AuditTime) / 2, a.AuditTime)
						END AS TimeMark,
						a.IsOnline
				FROM	Seq AS a
						FULL JOIN	Seq AS b
									ON	a.rn = b.rn - 1
				WHERE	a.IsOnline <> b.IsOnline
						OR a.IsOnline IS NULL
						OR b.IsOnline IS NULL
			) AS p
)
SELECT	b.TimeMark AS TimeFrom,
		a.TimeMark AS TimeTo,
		CASE a.IsOnline
			WHEN 1 THEN 'Yes'
			ELSE 'No'
		END  AS IsOnline
FROM	Periods AS a
		INNER JOIN	Periods AS b
					ON a.rn = b.rn + 1;

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jun 16 2010 11:49PM         154           0         124    0.591
  2 Jun 17 2010 12:12AM         122           0         140    0.229
  3 Jun 17 2010 12:35AM         122           0         125    0.208
  4 Jun 17 2010 12:58AM         122           0         110    0.215
  5 Jun 17 2010  1:21AM         122           0         110    0.248