SQL: Merge rows when value doesnt change in continuous time
create table StatusTable
(
SerialNumber varchar(15),
BeginDateTime datetime,
EndDateTime datetime,
StatusID int,
TimeInStateInMilliseconds int
)
go

insert StatusTable
values
('DASimulator1','2/25/20 16:15','2/25/20 16:16',0,60000)
,('DASimulator1','2/25/20 16:14','2/25/20 16:15',0,60000)
,('DASimulator1','2/25/20 16:13','2/25/20 16:14',5,60000)
,('DASimulator1','2/25/20 16:13','2/25/20 16:14',0,60000)
,('DASimulator1','2/24/20 23:20','2/24/20 23:21',3,60000)
,('DASimulator1','2/24/20 23:19','2/24/20 23:20',3,60000)
,('DASimulator1','2/24/20 23:18','2/24/20 23:19',3,60000)
,('DASimulator1','2/24/20 23:17','2/24/20 23:18',3,60000)
go

with base as
(
select a.*
    , CASE when b.SerialNumber is not null or c.SerialNumber is not null then 1 else 0 end as GroupStatus
from StatusTable a
        left join StatusTable b on a.BeginDateTime = b.EndDateTime and a.StatusID = b.StatusID
        left join StatusTable c on a.EndDateTime = c.BeginDateTime and a.StatusID = c.StatusID
)

select SerialNumber, MIN(BeginDateTime) as BeginDateTime, MAX(EndDateTime) as EndDateTime, StatusID, SUM(TimeInStateInMilliseconds) as TimeInStateInMilliseconds
from base
where GroupStatus = 1
group by SerialNumber, StatusID

union all

select SerialNumber, MIN(BeginDateTime) as BeginDateTime, MAX(EndDateTime) as EndDateTime, StatusID, SUM(TimeInStateInMilliseconds) as TimeInStateInMilliseconds
from base
where GroupStatus = 0
group by SerialNumber, StatusID

order by EndDateTime desc
go


here's what i came up with based on only the data set that you've shared. if there are other data variations in your full data set then you need to adjust for it.

firstly, i'm a bit confused on your desired output result set. the third row EndDateTime value matches the first row BeginDateTime value and they both have the same StatusID value? so based on what you said about continuous time, wouldn't these two rows be merged together?

my query behaves as such, if you run my code, it will merge row 1 and 4 together.

also, shouldn't the fourth row in your result set have a BeginDateTime value of 2020-02-24 23:17:00.000 or are you just grabbing the EndDateTime value and making that the BeginDateTime value if it matches?