Database Challenges

--mark_cowne_tsqlchallenge_23_V2.sql
 
-- 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
 
 
 

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.