SQL start time end time query based on the status Column

Question

Hi Folks,

Please help me here. I want to get Start datetime and End datetime based on the status column from source table.

Source table Script:
Create table Sourcetabel(Time_Stamp datetime,shift_id Varchar(50),Machine_Code Varchar(50),Line_Code Varchar(50),CompanyCode Varchar(50),PlantCode Varchar(50),Machine_status int)
Insert into Sourcetabel values('2020-10-05 15:00:00','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:00:08','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:00:33','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:00:52','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:01:09','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:01:25','S2','M1','L1','DXC','Unit_1',1)

Insert into Sourcetabel values('2020-10-05 15:01:43','S2','M1','L1','DXC','Unit_1',0)
Insert into Sourcetabel values('2020-10-05 15:02:06','S2','M1','L1','DXC','Unit_1',0)
Insert into Sourcetabel values('2020-10-05 15:02:34','S2','M1','L1','DXC','Unit_1',0)
Insert into Sourcetabel values('2020-10-05 15:02:54','S2','M1','L1','DXC','Unit_1',0)
Insert into Sourcetabel values('2020-10-05 15:03:12','S2','M1','L1','DXC','Unit_1',0)
Insert into Sourcetabel values('2020-10-05 15:03:31','S2','M1','L1','DXC','Unit_1',0)
Insert into Sourcetabel values('2020-10-05 15:03:49','S2','M1','L1','DXC','Unit_1',0)

Insert into Sourcetabel values('2020-10-05 15:04:05','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:04:05','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:06:29','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:07:12','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:07:31','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:07:50','S2','M1','L1','DXC','Unit_1',1)

Insert into Sourcetabel values('2020-10-05 15:08:06','S2','M1','L1','DXC','Unit_1',3)
Insert into Sourcetabel values('2020-10-05 15:08:30','S2','M1','L1','DXC','Unit_1',3)
Insert into Sourcetabel values('2020-10-05 15:08:44','S2','M1','L1','DXC','Unit_1',3)
Insert into Sourcetabel values('2020-10-05 15:08:59','S2','M1','L1','DXC','Unit_1',3)
Insert into Sourcetabel values('2020-10-05 15:09:17','S2','M1','L1','DXC','Unit_1',3)
Insert into Sourcetabel values('2020-10-05 15:09:37','S2','M1','L1','DXC','Unit_1',3)
Insert into Sourcetabel values('2020-10-05 15:09:55','S2','M1','L1','DXC','Unit_1',3)
Insert into Sourcetabel values('2020-10-05 15:10:16','S2','M1','L1','DXC','Unit_1',3)

Insert into Sourcetabel values('2020-10-05 15:10:36','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:10:50','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:11:06','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:11:22','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:11:42','S2','M1','L1','DXC','Unit_1',1)
Insert into Sourcetabel values('2020-10-05 15:11:56','S2','M1','L1','DXC','Unit_1',1)

Insert into Sourcetabel values('2020-10-05 15:12:25','S2','M1','L1','DXC','Unit_1',4)
Insert into Sourcetabel values('2020-10-05 15:12:46','S2','M1','L1','DXC','Unit_1',4)
Insert into Sourcetabel values('2020-10-05 15:13:00','S2','M1','L1','DXC','Unit_1',4)
Insert into Sourcetabel values('2020-10-05 15:13:15','S2','M1','L1','DXC','Unit_1',4)
Insert into Sourcetabel values('2020-10-05 15:13:30','S2','M1','L1','DXC','Unit_1',4)

Expected Output:

Answer 1

Check a method that is based on multiple Common Table Expressions (CTE):

;
with Q1 as
(
    select *, 
        LAG(Machine_status) over (order by Time_Stamp) as prev, 
        LEAD(Machine_status) over (order by Time_Stamp) as next 
    from Sourcetabel
),
Q2 as
(
    select *,
        ROW_NUMBER() over (order by Time_Stamp) as rn
    from  Q1
    where prev is null or next is null or not (Machine_status = prev and Machine_status = next)
),
Q3 as
(
    select a.shift_id, a.Machine_Code, a.Line_Code, a.CompanyCode, a.PlantCode, a.Machine_status,
        a.Time_Stamp as Start,
        b.Time_Stamp as [End]
    from Q2 as a
    inner join Q2 as b on b.rn = a.rn + 1
    where a.rn % 2 <> 0
)
select * from Q3
order by Start

It is not clear if a grouping by some columns is required.