SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table status (username varchar(50), NewState varchar(10), StartTime time, EndTime time)

insert into status (username, NewState, StartTime, EndTime) values
('joe', 'st0', '08:00:00', '08:01:00'),
('joe', 'st1', '08:01:00', '09:00:00'),
('alice', 'st1', '08:30:00', '09:01:00'),
('joe', 'stX', '09:00:00', '11:00:00'),
('alice', 'st2', '09:01:00', '11:50:00'),
('joe', 'st3', '11:20:00', '14:50:00'),
('joe', 'stx', '14:50:00', '18:50:00')

select * from status


username
NewState
StartTime
EndTime


joe
st0
08:00:00.0000000
08:01:00.0000000

joe
st1
08:01:00.0000000
09:00:00.0000000

alice
st1
08:30:00.0000000
09:01:00.0000000

joe
stX
09:00:00.0000000
11:00:00.0000000

alice
st2
09:01:00.0000000
11:50:00.0000000

joe
st3
11:20:00.0000000
14:50:00.0000000

joe
stx
14:50:00.0000000
18:50:00.0000000


select s.username,
       isnull(tstart.StartTime, s.StartTime) as StartTime,
       tend.EndTime
from   status s
  outer apply ( select top 1
                       s2.StartTime
                from   status s2
                  outer apply ( select top 1 s3.StartTime 
                                from   status s3 
                                where  s3.username = s.username 
                                and    s3.StartTime < s.StartTime 
                                and    s3.NewState = 'stX'
                                order by s3.StartTime desc
                              ) s4
                where  s2.username = s.username
                and    s2.StartTime < s.StartTime 
                and    (s4.StartTime is null or s2.StartTime > s4.StartTime)
                order by s2.StartTime
              ) tstart 
  outer apply ( select top 1
                       s2.EndTime
                from   status s2
                where  s2.username = s.username
                and    s2.EndTime >= s.EndTime
                and    s2.NewState = 'stX'
                order by s2.EndTime
              ) tend
group by  s.username,
          isnull(tstart.StartTime, s.StartTime),
          tend.EndTime
order by isnull(tstart.StartTime, s.StartTime)



username
StartTime
EndTime


joe
08:00:00.0000000
11:00:00.0000000

alice
08:30:00.0000000
null

joe
11:20:00.0000000
18:50:00.0000000


drop table status

username	NewState	StartTime	EndTime
joe	st0	08:00:00.0000000	08:01:00.0000000
joe	st1	08:01:00.0000000	09:00:00.0000000
alice	st1	08:30:00.0000000	09:01:00.0000000
joe	stX	09:00:00.0000000	11:00:00.0000000
alice	st2	09:01:00.0000000	11:50:00.0000000
joe	st3	11:20:00.0000000	14:50:00.0000000
joe	stx	14:50:00.0000000	18:50:00.0000000