Database Challenges

--muhammad_al_pasha_tsqlchallenge_23_v1.sql
WITH GroupingFactorsCTE AS
(
   SELECT AuditTime, IsOnline, ROW_NUMBER() OVER(ORDER BY AuditTime) - ROW_NUMBER() OVER(ORDER BY IsOnline, AuditTime) AS grouping_factor
     FROM TC23
)
, MinMaxTimesCTE AS
(
   SELECT MIN(AuditTime) AS min_time, MAX(AuditTime) AS max_time, IsOnline, ROW_NUMBER() OVER(ORDER BY MIN(AuditTime)) AS row_num
     FROM GroupingFactorsCTE
    GROUP BY IsOnline, grouping_factor
)
, MinMaxAndMidPointTimesCTE AS
(
   SELECT T1.min_time,
          ISNULL(T2.max_time, T1.max_time) AS max_time,
          DATEADD(SECOND, DATEDIFF(SECOND, T1.max_time, T2.min_time) / 2, T1.max_time) mid_point_time,
          ROW_NUMBER() OVER(ORDER BY T1.min_time) AS row_num,
          T1.IsOnline
     FROM MinMaxTimesCTE AS T1
          LEFT OUTER JOIN
          MinMaxTimesCTE AS T2
          ON T2.row_num = T1.row_num + 1
)
SELECT ISNULL(T1.mid_point_time, DATEADD(HOUR, DATEDIFF(HOUR, '20100101', DATEADD(SECOND, -1, T2.min_time)), '20100101')) AS TimeFrom,
       ISNULL(T2.mid_point_time, DATEADD(HOUR, DATEDIFF(HOUR, '20100101', DATEADD(SECOND, 1, T1.max_time)) + 1, '20100101')) AS TimeTo,
       CASE T2.IsOnline 
            WHEN 1 THEN 'Yes'
            ELSE 'No' 
       END AS IsOnline
  FROM MinMaxAndMidPointTimesCTE AS T1
       RIGHT OUTER JOIN
       MinMaxAndMidPointTimesCTE AS T2
       ON T2.row_num = T1.row_num + 1
 ORDER BY T1.mid_point_time
 

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.