add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table CL(LINE INT, TIMESTAMP DATETIME, CL_Name VARCHAR(50), Status INT);

Insert Into CL Values
(2,'2023-01-03 11:25:33.917','FILM',1),
(2,'2023-01-03 11:20:33.917','FILM',1),
(2,'2023-01-03 11:15:33.910','FILM',0),
(2,'2023-01-03 11:10:33.903','FILM',0),
(2,'2023-01-03 11:05:33.900','FILM',0),
(2,'2023-01-04 12:55:33.917','FILM',1),
(2,'2023-01-04 12:37:33.917','FILM',1),
(2,'2023-01-04 12:34:33.910','FILM',0),
(2,'2023-01-04 12:20:33.903','FILM',0),
(2,'2023-01-04 12:05:33.900','FILM',0);
10 rows affected
SELECT *,
IIF(Status <> LAG(Status, 1, Status) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP), 1 , 0) flag
FROM CL
LINE TIMESTAMP CL_Name Status flag
2 2023-01-03 11:05:33.900 FILM 0 0
2 2023-01-03 11:10:33.903 FILM 0 0
2 2023-01-03 11:15:33.910 FILM 0 0
2 2023-01-03 11:20:33.917 FILM 1 1
2 2023-01-03 11:25:33.917 FILM 1 0
2 2023-01-04 12:05:33.900 FILM 0 1
2 2023-01-04 12:20:33.903 FILM 0 0
2 2023-01-04 12:34:33.910 FILM 0 0
2 2023-01-04 12:37:33.917 FILM 1 1
2 2023-01-04 12:55:33.917 FILM 1 0
WITH Check_Changes AS
(
SELECT *,
IIF(Status <> LAG(Status, 1, Status) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP), 1 , 0) flag
FROM CL
)
SELECT *,
SUM(flag) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP) as v1,
CASE WHEN SUM(flag) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP) % 2 = Status
THEN 0 ELSE 1 END as v2,
SUM(flag) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP) +
CASE WHEN SUM(flag) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP) % 2 = Status
THEN 0 ELSE 1 END AS grp
FROM Check_Changes
LINE TIMESTAMP CL_Name Status flag v1 v2 grp
2 2023-01-03 11:05:33.900 FILM 0 0 0 0 0
2 2023-01-03 11:10:33.903 FILM 0 0 0 0 0
2 2023-01-03 11:15:33.910 FILM 0 0 0 0 0
2 2023-01-03 11:20:33.917 FILM 1 1 1 0 1
2 2023-01-03 11:25:33.917 FILM 1 0 1 0 1
2 2023-01-04 12:05:33.900 FILM 0 1 2 0 2
2 2023-01-04 12:20:33.903 FILM 0 0 2 0 2
2 2023-01-04 12:34:33.910 FILM 0 0 2 0 2
2 2023-01-04 12:37:33.917 FILM 1 1 3 0 3
2 2023-01-04 12:55:33.917 FILM 1 0 3 0 3
/* check if the status is changed over the increasing time,
and set a value of 1 whenever a change is occur
*/
WITH Check_Changes AS
(
SELECT *,
IIF(Status <> LAG(Status, 1, Status) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP), 1 , 0) flag
FROM CL
),
/* create groups using a running sum of the calculated flag in
the previous CTE. This will give the same number for the
consecutive similar status values i.e. for (0000,11,000,11) it
will be (0000,11,222,33)
*/
Craete_Groups AS
(
SELECT *,
SUM(flag) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP) +
CASE WHEN SUM(flag) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP) % 2 = Status
THEN 0 ELSE 1 END AS grp
FROM Check_Changes
/* CASE WHEN SUM(flag) OVER ... is to check if the status begins with a 0 value.
In the typical case, the even groups will be within status=0 and the odd groups will be within status=1.
This will add 1 to the group value if the status does not start with 0 i.e. for status = (1111,000,111)
it will be (1111,222,333) instead of (0000,111,222)
*/
)
/* now you can connect every two consecutive groups together by using grp/2
i.e. groups (0000,11,222,33) will be (0000,00,111,11)
*/
SELECT LINE, CL_Name,
MIN(CASE WHEN Status = 0 THEN TIMESTAMP END) StartTime,
MAX(CASE WHEN Status = 1 THEN TIMESTAMP END) EndTime,
DATEDIFF(MINUTE,
MIN(CASE WHEN Status = 0 THEN TIMESTAMP END),
MAX(CASE WHEN Status = 1 THEN TIMESTAMP END)
LINE CL_Name StartTime EndTime TotalTime
2 FILM 2023-01-03 11:05:33.900 2023-01-03 11:25:33.917 20
2 FILM 2023-01-04 12:05:33.900 2023-01-04 12:55:33.917 50 
Warning: Null value is eliminated by an aggregate or other SET operation.