SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
 Jul 25 2020 11:26:55 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE test (time varchar(20), action  varchar(20));

INSERT INTO test VALUES
  ('03:00:00', 'block')
, ('04:00:00', 'unblock')
, ('05:00:00', 'block')
, ('06:00:00', 'unblock')
, ('07:00:00', 'unblock')
, ('08:00:00', 'block')
;
6 rows affected

WITH cte1 AS (
         SELECT *, SUM(CASE WHEN action = 'block' THEN 1 END) OVER (ORDER BY time) AS block_no FROM test
     )
SELECT * FROM cte1
 ORDER BY time
;


time
action
block_no


03:00:00
block
1

04:00:00
unblock
1

05:00:00
block
2

06:00:00
unblock
2

07:00:00
unblock
2

08:00:00
block
3


Warning: Null value is eliminated by an aggregate or other SET operation.


WITH cte1 AS (
         SELECT *, SUM(CASE WHEN action = 'block' THEN 1 END) OVER (ORDER BY time) AS block_no FROM test
     )
SELECT *, MIN(time) OVER (PARTITION BY block_no) AS block_time FROM cte1
 ORDER BY time
;


time
action
block_no
block_time


03:00:00
block
1
03:00:00

04:00:00
unblock
1
03:00:00

05:00:00
block
2
05:00:00

06:00:00
unblock
2
05:00:00

07:00:00
unblock
2
05:00:00

08:00:00
block
3
08:00:00


Warning: Null value is eliminated by an aggregate or other SET operation.

time	action	block_no
03:00:00	block	1
04:00:00	unblock	1
05:00:00	block	2
06:00:00	unblock	2
07:00:00	unblock	2
08:00:00	block	3