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

 
-- Addition of bizarre rule to move the start hour back by one if it is exactly on the hour
 
WITH OrderedTC23 AS (
SELECT AuditTime,IsOnline,
       ROW_NUMBER() OVER(ORDER BY AuditTime) AS rn,
       ROW_NUMBER() OVER(PARTITION BY IsOnline ORDER BY AuditTime) AS rnPart
FROM TC23),
Groups AS (
SELECT MIN(rn) AS rnFrom,
       MIN(AuditTime) AS TimeFrom,
       MAX(rn) AS rnTo,
       MAX(AuditTime) AS TimeTo,
       IsOnline
FROM OrderedTC23
GROUP BY rn-rnPart,IsOnline)
SELECT CASE WHEN s.AuditTime IS NOT NULL
            THEN DATEADD(Second,DATEDIFF(Second,s.AuditTime,g.TimeFrom)/2,s.AuditTime)
            ELSE DATEADD(Hour, DATEDIFF(Hour, 0, g.TimeFrom) - 1 + SIGN(DATEPART(Minute,g.TimeFrom)+DATEPART(Second,g.TimeFrom)+DATEPART(Millisecond,g.TimeFrom)), 0)
            END AS TimeFrom,
       CASE WHEN e.AuditTime IS NOT NULL
            THEN DATEADD(Second,DATEDIFF(Second,g.TimeTo,e.AuditTime)/2,g.TimeTo)
            ELSE DATEADD(Hour, DATEDIFF(Hour, 0, g.TimeTo)+1, 0)
            END AS TimeTo,
       CASE WHEN g.IsOnline=0 THEN 'No' ELSE 'Yes' END AS IsOnline
FROM Groups g
LEFT OUTER JOIN OrderedTC23 s ON s.rn+1=g.rnFrom AND s.IsOnline<>g.IsOnline AND s.AuditTime<=g.TimeFrom
LEFT OUTER JOIN OrderedTC23 e ON e.rn-1=g.rnTo AND e.IsOnline<>g.IsOnline AND e.AuditTime>=g.TimeTo
ORDER BY g.rnFrom;
 
 
 

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jun 17 2010 12:03AM          93           0         156    0.248
  2 Jun 17 2010 12:25AM          93           0         109    0.219
  3 Jun 17 2010 12:48AM          93           0         109    0.246
  4 Jun 17 2010  1:11AM          93           0         109    0.208
  5 Jun 17 2010  1:34AM          93           0         109    0.210