SQL: Merge rows when value doesnt change in continuous time

Question

I am new to SQL and I need to summarize a table that has this information: Serial Number, StartDate, EndDate, StatusID and TimeInState, what I need is to merge rows that keep the same StatusId during a continuous time (EndDate of row is same as StartDate of the other), for example, for this case: Dataset

I need to output this: Desired Output Table

I cant come with a solution yet since I think I cant use Group By, I need to merge rows with specific SerialNumber when the StatusId doesn't change in a continuous time.

Answer 1

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?