Database Challenges

--erick_bailey_tsqlchallenge_23_v7.sql
WITH    Ordered
          AS ( SELECT   DATEADD(ms, DATEPART(ms, AuditTime) * -1, AuditTime) AS AuditTime,
                        isOnline,
                        ROW_NUMBER() OVER ( ORDER BY AuditTime ) AS ranking
               FROM     dbo.TC23
             ) ,
        RecordSet
          AS ( SELECT   DATEADD(ss,
                                DATEDIFF(ss, o1.AuditTime, o2.AuditTime) / 2,
                                o1.AuditTime) AS timeto,
                        ROW_NUMBER() OVER ( ORDER BY DATEADD(ss,
                                                             DATEDIFF(ss, o1.AuditTime, o2.AuditTime)
                                                             / 2, o1.AuditTime) ) reorder,
                        CASE WHEN o1.IsOnline = 1 THEN 'YES'
                             WHEN o1.IsOnline = 0 THEN 'NO'
                             ELSE '?'
                        END AS ISOnLine
               FROM     ordered o1
                        INNER JOIN Ordered o2 ON o1.ranking + 1 = o2.ranking
                                                 AND ( o1.isonline <> o2.isonline
                                                       OR o1.isonline IS NULL
                                                       OR o2.isonline IS null
                                                     )
             ) ,
        RecordSet1
          AS ( SELECT   CASE WHEN DATEPART(mi, audittime) = 0
                                  AND DATEPART(ss, audittime) = 0
                             THEN DATEADD(hh, -1,
                                          DATEADD(hh,
                                                  DATEDIFF(hh, '19000101',
                                                           audittime),
                                                  '19000101'))
                             ELSE DATEADD(hh,
                                          DATEDIFF(hh, '19000101', audittime),
                                          '19000101')
                        END AS timeto,
                        0 AS reorder,
                        CASE WHEN IsOnline = 1 THEN 'YES'
                             WHEN IsOnline = 0 THEN 'NO'
                             ELSE '?'
                        END AS ISOnLine
               FROM     ordered
               WHERE    Audittime IN ( SELECT   MIN(audittime)
                                       FROM     ordered )
               UNION
               SELECT   DATEADD(hh, DATEDIFF(hh, '19000101', audittime) + 1,
                                '19000101') AS TimeTo,
                        MAX(reorder) + 1 AS reorder,
                        CASE WHEN TC23.IsOnline = 1 THEN 'YES'
                             WHEN TC23.IsOnline = 0 THEN 'NO'
                             ELSE '?'
                        END AS ISOnLine
               FROM     dbo.TC23,
                        RecordSet
               WHERE    AuditTime in ( SELECT   MAX(AuditTime)
                                       FROM     TC23 )
               GROUP BY Audittime,
                        tc23.IsOnLine
               UNION
               SELECT   timeto,
                        reorder,
                        ISOnLine
               FROM     RecordSet
             )
    SELECT  rs1.timeto AS TimeFrom,
            rs2.timeto AS TimeTo,
            rs2.ISOnLine
    FROM    RecordSet1 AS rs1
            INNER JOIN RecordSet1 AS rs2 ON rs1.reorder + 1 = rs2.reorder
    ORDER BY rs1.TimeTo

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.