SQL Server merge datetime rows in one row and splits when values of other columns change

I'm trying to create a view in SQL which merge start datetime and last datetime and splits when value column changed knowing that the value of the date difference is not fixed. For example I have a table name Sensors:

SensorID
StartTime
EndTime
value

14033
2023-05-01 00:00:00.000
2023-05-01 00:05:00.000
0

14033
2023-05-01 00:05:00.000
2023-05-01 00:20:00.000
1

14033
2023-05-01 00:20:00.000
2023-05-01 03:00:00.000
1

14033
2023-05-01 03:00:00.000
2023-05-01 12:00:00.000
1

14033
2023-05-01 12:00:00.000
2023-05-01 20:00:00.000
0

14033
2023-05-01 20:00:00.000
2023-05-01 22:59:59.000
0

the result must be like the table below:

SensorID
StartTime
EndTime
value

14033
2023-05-01 00:00:00.000
2023-05-01 00:05:00.000
0

14033
2023-05-01 00:05:00.000
2023-05-01 12:00:00.000
1

14033
2023-05-01 12:00:00.000
2023-05-01 22:59:59.000
0

Base on this sample, here is some piece of code, inspired by Itzik Ben-Gan , Special Islands solution here is the link.

(I added CTEs to be better understood)

CREATE VIEW dbo.vw_Sensors
AS

    with CTE_Source AS
    (
        SELECT *    ,
            LAG(EndTime,1,NULL) OVER(PARTITION BY SensorID ORDER BY StartTime) as EndTime_prev,
            LAG(Value,1,NULL) OVER(PARTITION BY SensorID ORDER BY StartTime) as Value_prev
        FROM dbo.Sensors
    )
    ,CTE_Source2 AS
    (
        SELECT * ,
            CASE WHEN s.StartTime <> ISNULL(s.EndTime_prev,'19000101') THEN 1
                 WHEN s.Value <> ISNULL(s.Value_prev,-1) THEN 1
                 ELSE 0 
            END as IsGrp
        FROM CTE_Source as s
    )
    ,CTE_Source3 AS
    (
        SELECT * ,
            SUM(s.isGrp) OVER(PARTITION BY s.SensorID  ORDER BY StartTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sumGrp
        FROM CTE_Source2 as s
    )
    SELECT s.SensorID,
        MIN(s.StartTime) as StartTime,
        MAX(s.EndTime) as EndTime,
        MAX(s.Value) as Value
    FROM CTE_Source3 as s
    GROUP BY 
        s.SensorID,
        s.sumGrp
GO

output:

SensorID
StartTime
EndTime
Value

14033
2023-05-01 00:00:00.000
2023-05-01 00:05:00.000
0

14033
2023-05-01 00:05:00.000
2023-05-01 12:00:00.000
1

14033
2023-05-01 12:00:00.000
2023-05-01 22:59:59.000
0

dbfiddle

In step 1 (first select), we just calculate the previous values for EndTime and for Value

SensorID
StartTime
EndTime
value
EndTime_prev
Value_prev

14033
2023-05-01 00:00:00.000
2023-05-01 00:05:00.000
0
null
null

14033
2023-05-01 00:05:00.000
2023-05-01 00:20:00.000
1
2023-05-01 00:05:00.000
0

14033
2023-05-01 00:20:00.000
2023-05-01 03:00:00.000
1
2023-05-01 00:20:00.000
1

The step 2, if there is a difference between StartTime and previous EndTime or a difference between Value and previous Value , then we mark this row as 1 = IsGrp

SensorID
StartTime
EndTime
value
EndTime_prev
Value_prev
IsGrp

14033
2023-05-01 00:00:00.000
2023-05-01 00:05:00.000
0
null
null
1

14033
2023-05-01 00:05:00.000
2023-05-01 00:20:00.000
1
2023-05-01 00:05:00.000
0
1

14033
2023-05-01 00:20:00.000
2023-05-01 03:00:00.000
1
2023-05-01 00:20:00.000
1
0

In the step 3, we apply SUM on this new field (isGrp) .

SensorID
StartTime
EndTime
value
EndTime_prev
Value_prev
IsGrp
sumGrp

14033
2023-05-01 00:00:00.000
2023-05-01 00:05:00.000
0
null
null
1
1

14033
2023-05-01 00:05:00.000
2023-05-01 00:20:00.000
1
2023-05-01 00:05:00.000
0
1
2

14033
2023-05-01 00:20:00.000
2023-05-01 03:00:00.000
1
2023-05-01 00:20:00.000
1
0
2

14033
2023-05-01 03:00:00.000
2023-05-01 12:00:00.000
1
2023-05-01 03:00:00.000
1
0
2

The final step/select is just to aggregate these information to display the desired output

StackExchange Database Administrators Q#327987, answer score: 6