Database Challenges

--Rami_reddy_Challenge23_V4.sql
;with cte as
(
	select AuditTime,IsOnLine, ROW_NUMBER() over (order by AuditTime)  - ROW_NUMBER() over (order by IsonLine,AuditTime)  as GroupId	
	from TC23		
),
cte1 as
(
	select MIN(AuditTime) as TimeFrom,MAX(AuditTime) as TimeTo,	
	case when IsOnLine = 1 then 'Yes' else 'No' end AS IsOnLine
	,ROW_NUMBER() over (order by Min(AuditTime)) as rn from cte
	group by GroupId,IsOnLine
)
select 
case
	when c1.rn = 1 then dateadd(hour,datediff(hour,0,dateadd(minute,-1,c1.TimeFrom)),0) 
	else  DATEADD(second,datediff(second,c3.TimeTo,c1.TimeFrom)/2,c3.TimeTo) 
end as TimeFrom,
case
	when c2.rn IS null  then DATEADD(HOUR,DATEDIFF(hour,0,DATEADD(minute,60,c1.TimeTo)),0)
	else  DATEADD(second,datediff(second,c1.TimeTo,c2.TimeFrom)/2,c1.TimeTo)
end as TimeTo, c1.IsOnLine
from cte1	c1 
left outer join cte1 c2 on c1.rn + 1 = c2.rn  
left outer join cte1 c3 on c1.rn - 1 = c3.rn
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.