0

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.

2
  • 2
    Please see How to Ask. Screencaps of data are less helpful than text. Please see also minimal reproducible example & welcome to the network :) Feb 25 '20 at 19:44
  • 1
    As @PeterVandivier says, please provide tables as DDL (CREATE TABLE foo...) and data as DML (INSERT INTO foo VALUES (....). Please see the reasons why posting images is a bad idea on this link. p.s. welcome to the forum! :-) Feb 25 '20 at 20:12
0
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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.