Database Challenges

--spark_s_tsqlchallenge_23_v3.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) 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
	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)  
	 
	 union
	      
	 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)))
			   ), isOnline
	 from T
	 where T.idx = 1
	 
	 union
	 
	 select DATEADD(mi, 0-DATEPART(mi, auditTime), DATEADD(SS, 0 - DATEPART(ss, auditTime), DATEADD(hh, 1, auditTime))),
			isOnline
	 from T
	 where T.idx = (select MAX(idx) from T)     
),
TTT (midTime, isOnline, idx)
as(
	select midTime, isOnline, 
		   ROW_NUMBER() over (order by midTime)
	from TT
)
select A1.midTime as TimeFrom, A2.midTime as TimeTo,
      case A1.isOnline
		when 1 then 'Yes'
		when 0 then 'No'
	end as IsOnline 
from TTT A1
     inner join TTT A2 on (A1.idx = A2.idx - 1)
     

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.