By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Machine_Status
(
[Row] INT,
[Timestamp] DateTime,
Running Bit
);
INSERT dbo.Machine_Status ([Row], [Timestamp], Running) VALUES
('1', '20230110 08:20:10', '0'),
('2', '20230110 08:21:10', '0'),
('3', '20230110 08:21:30', '1'),
('4', '20230110 08:22:30', '1'),
('5', '20230110 08:23:30', '1'),
('6', '20230110 08:23:40', '0'),
('7', '20230110 08:24:40', '0'),
('8', '20230110 08:25:30', '1'),
('9', '20230110 08:26:30', '1'),
('10', '20230110 08:27:30', '1'),
('11', '20230110 08:28:30', '1'),
('12', '20230110 08:28:55', '0'),
('13', '20230110 08:29:55', '0');
13 rows affected
WITH src AS
(
SELECT Island, mint = MIN([Timestamp]), maxt = MAX([Timestamp])
FROM
(
SELECT [Timestamp], Island =
ROW_NUMBER() OVER (ORDER BY [Timestamp]) -
ROW_NUMBER() OVER (PARTITION BY Running ORDER BY [Timestamp])
FROM dbo.Machine_Status
) AS x GROUP BY Island
)
SELECT TOP (1) delta =
(DATEDIFF(second, mint, LEAD(mint,1) OVER (ORDER BY island)))
FROM src ORDER BY delta DESC;
| delta |
|---|
| 205 |