Database Challenges

--spark_s_tsqlchallenge_23_v2.sql
with T (auditTime, isOnline, idx) as
(
select AuditTime, IsOnline, ROW_NUMBER() over (order by auditTime)
from TC23
where AuditTime is not null AND IsOnline is not null
),
TT (midTime, isOnline, idx) as
(
select case 
		when T1.auditTime > T2.auditTime 
		then Dateadd(ss, DATEDIFF(SS, T2.auditTime, T1.auditTime)/2, T2.auditTime)
	    else Dateadd(ss, DATEDIFF(SS, T1.auditTime, T2.auditTime)/2, T1.auditTime)
       end, 
       case
		when T1.idx > T2.idx
		then 1
		else 0
       end,
       ROW_NUMBER() over (order by T1.auditTime)
from (select auditTime, idx
      from T 
      where isOnline = 1) T1
     inner join (select auditTime, idx
      from T 
      where isOnline = 0) T2 on (T1.idx = T2.idx - 1 or T1.idx = T2.idx + 1)  
),
TTT (TimeFrom, TimeTo, IsOnline) as
(   
    select DATEADD(hh, CONVERT(INT, CONVERT(BIT, DATEPART(mi, T.auditTime)+DATEPART(ss,T.auditTime))), 
               DATEADD(mi, 0-DATEPART(mi, T.auditTime), DATEADD(SS, 0 - DATEPART(ss, T.auditTime), DATEADD(hh, -1, T.auditTime)))
           ),
           (select min(TT.midTime) from TT where TT.idx = 1),
           T.isOnline
    from T
    where T.idx = 1 
    
    union
    
	select T1.midTime, (select MIN(midTime) from TT where idx = T1.idx + 1), 
	        T1.isOnline  
	from TT as T1
)
select 
	TimeFrom,
	ISNULL(TimeTo,(select MAX(DATEADD(mi, 0-DATEPART(mi, auditTime), DATEADD(SS, 0 - DATEPART(ss, auditTime), DATEADD(hh, 1, auditTime)))) 
	               from T)) as TimeTo,
	case IsOnline
		when 1 then 'Yes'
		when 0 then 'No'
	end as IsOnline
from TTT 
order by TimeFrom

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.