Database Challenges

--leszek_g_tsqlchallenge_23_v4.sql
;with 
numbered as
(
 select 
	t.AuditTime
	,t.IsOnline
	,row_number() over(order by t.audittime) numb
 from tc23 t
)
,crosstimes as
(
 select
	x.crosstime
	,x.IsOnline 
	,row_number() over(order by x.crosstime) numb
 from
	(
	 select
		dateadd(s,datediff(s,a.AuditTime,b.AuditTime)/2,a.AuditTime) crosstime
		,b.IsOnline
	 from numbered a
	 join numbered b on a.numb+1=b.numb and a.IsOnline!=b.IsOnline
	 union
	 select top 1
		dateadd(s,-datepart(s,dateadd(s,-1,t.AuditTime)),dateadd(mi,-datepart(mi,dateadd(s,-1,t.AuditTime)),dateadd(s,-1,t.AuditTime)))
		,t.IsOnline
	 from  tc23 t
	 order by t.AuditTime
	 union
	 select top 1
		dateadd(hh,1,dateadd(s,-datepart(s,t.AuditTime),dateadd(mi,-datepart(mi,t.AuditTime),t.AuditTime)))
		,~t.IsOnline
	 from  tc23 t
	 order by t.AuditTime desc
	) x
)

select
	convert(char,a1.crosstime,120) TimeFrom
	,convert(char,a2.crosstime,120) TimeTo
	,case a1.IsOnline when 1 then 'Yes' else 'No' end IsOnline
from crosstimes a1
join crosstimes a2 on a1.numb+1=a2.numb
order by a2.numb

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.