SQL Server 2017

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

CREATE TABLE data_point
(
  room_code VARCHAR(25),
  occupancy TINYINT NOT NULL CHECK (occupancy IN (0,1)),
  room_time DATETIME,
  day_from_date VARCHAR(15)
);
INSERT INTO data_point (room_code, occupancy, room_time, day_from_date)
VALUES
  ('EW1A-03-08', 0, '10/07/2019 08:00', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 08:30', 'Wednesday'),
  ('EW1A-03-08', 1, '10/07/2019 08:30', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 09:00', 'Wednesday'),
  ('EW1A-03-08', 1, '10/07/2019 09:00', 'Wednesday'),
  ('EW1A-03-08', 1, '10/07/2019 09:30', 'Wednesday'),
  ('EW1A-03-08', 1, '10/07/2019 10:00', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 10:30', 'Wednesday'),
  ('EW1A-03-08', 1, '10/07/2019 10:30', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 11:00', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 11:30', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 12:00', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 12:30', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 13:00', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 13:30', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 14:00', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 14:30', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 15:00', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 15:30', 'Wednesday'),
  ('EW1A-03-08', 1, '10/07/2019 15:30', 'Wednesday'),
  ('EW1A-03-08', 0, '10/07/2019 16:00', 'Wednesday');
21 rows affected

SELECT 
       room_code, 
       ROW_NUMBER() OVER (ORDER BY room_time) AS rn,  
       occupancy,
       room_time,
       day_from_date
FROM data_point;


room_code
rn
occupancy
room_time
day_from_date


EW1A-03-08
1
0
07/10/2019 08:00:00
Wednesday

EW1A-03-08
2
0
07/10/2019 08:30:00
Wednesday

EW1A-03-08
3
1
07/10/2019 08:30:00
Wednesday

EW1A-03-08
4
0
07/10/2019 09:00:00
Wednesday

EW1A-03-08
5
1
07/10/2019 09:00:00
Wednesday

EW1A-03-08
6
1
07/10/2019 09:30:00
Wednesday

EW1A-03-08
7
1
07/10/2019 10:00:00
Wednesday

EW1A-03-08
8
0
07/10/2019 10:30:00
Wednesday

EW1A-03-08
9
1
07/10/2019 10:30:00
Wednesday

EW1A-03-08
10
0
07/10/2019 11:00:00
Wednesday

EW1A-03-08
11
0
07/10/2019 11:30:00
Wednesday

EW1A-03-08
12
0
07/10/2019 12:00:00
Wednesday

EW1A-03-08
13
0
07/10/2019 12:30:00
Wednesday

EW1A-03-08
14
0
07/10/2019 13:00:00
Wednesday

EW1A-03-08
15
0
07/10/2019 13:30:00
Wednesday

EW1A-03-08
16
0
07/10/2019 14:00:00
Wednesday

EW1A-03-08
17
0
07/10/2019 14:30:00
Wednesday

EW1A-03-08
18
0
07/10/2019 15:00:00
Wednesday

EW1A-03-08
19
0
07/10/2019 15:30:00
Wednesday

EW1A-03-08
20
1
07/10/2019 15:30:00
Wednesday

EW1A-03-08
21
0
07/10/2019 16:00:00
Wednesday


WITH cte1 AS
(
  SELECT d.room_code,
         ROW_NUMBER() OVER (ORDER BY room_time) AS rn,
         LAG(d.occupancy) OVER (ORDER BY room_time) AS the_lag,
         d.occupancy, 
         LEAD(d.occupancy) OVER (ORDER BY room_time) AS the_lead,
         LEAD(d.occupancy, 2) OVER (ORDER BY room_time) AS the_2_lead,
         d.room_time, d.day_from_date
  FROM data_point d
)
SELECT * FROM cte1;


room_code
rn
the_lag
occupancy
the_lead
the_2_lead
room_time
day_from_date


EW1A-03-08
1
null
0
0
1
07/10/2019 08:00:00
Wednesday

EW1A-03-08
2
0
0
1
0
07/10/2019 08:30:00
Wednesday

EW1A-03-08
3
0
1
0
1
07/10/2019 08:30:00
Wednesday

EW1A-03-08
4
1
0
1
1
07/10/2019 09:00:00
Wednesday

EW1A-03-08
5
0
1
1
1
07/10/2019 09:00:00
Wednesday

EW1A-03-08
6
1
1
1
0
07/10/2019 09:30:00
Wednesday

EW1A-03-08
7
1
1
0
1
07/10/2019 10:00:00
Wednesday

EW1A-03-08
8
1
0
1
0
07/10/2019 10:30:00
Wednesday

EW1A-03-08
9
0
1
0
0
07/10/2019 10:30:00
Wednesday

EW1A-03-08
10
1
0
0
0
07/10/2019 11:00:00
Wednesday

EW1A-03-08
11
0
0
0
0
07/10/2019 11:30:00
Wednesday

EW1A-03-08
12
0
0
0
0
07/10/2019 12:00:00
Wednesday

EW1A-03-08
13
0
0
0
0
07/10/2019 12:30:00
Wednesday

EW1A-03-08
14
0
0
0
0
07/10/2019 13:00:00
Wednesday

EW1A-03-08
15
0
0
0
0
07/10/2019 13:30:00
Wednesday

EW1A-03-08
16
0
0
0
0
07/10/2019 14:00:00
Wednesday

EW1A-03-08
17
0
0
0
0
07/10/2019 14:30:00
Wednesday

EW1A-03-08
18
0
0
0
1
07/10/2019 15:00:00
Wednesday

EW1A-03-08
19
0
0
1
0
07/10/2019 15:30:00
Wednesday

EW1A-03-08
20
0
1
0
null
07/10/2019 15:30:00
Wednesday

EW1A-03-08
21
1
0
null
null
07/10/2019 16:00:00
Wednesday


WITH cte1 AS
(
  SELECT d.room_code,
         ROW_NUMBER() OVER (ORDER BY room_time) AS rn,
         LAG(d.occupancy) OVER (ORDER BY room_time) AS the_lag,
         d.occupancy, 
         LEAD(d.occupancy) OVER (ORDER BY room_time) AS the_lead,
         LEAD(d.occupancy, 2) OVER (ORDER BY room_time) AS the_2_lead,
         d.room_time, d.day_from_date
  FROM data_point d
),
cte2 AS
(
  SELECT room_code, rn, the_lag, occupancy, the_lead, the_2_lead, room_time, day_from_date  FROM cte1
  WHERE (occupancy = 0 AND the_lead = 1 AND the_2_lead !=1)
),
cte3 AS
(
  SELECT c2.rn AS rn2, c1.rn AS rn1
  FROM cte2 c2
  JOIN cte1 c1 
  ON c2.rn + 1  = c1.rn
)
SELECT t1.room_code, t1.rn, t1.occupancy, t1.room_time, t1.day_from_date
FROM cte1 t1 WHERE rn IN (SELECT rn2 FROM cte3)
UNION
SELECT t2.room_code, t2.rn, t2.occupancy, t2.room_time, t2.day_from_date
FROM cte1 t2 WHERE rn IN (SELECT rn1 FROM cte3)
ORDER BY day_from_date



room_code
rn
occupancy
room_time
day_from_date


EW1A-03-08
2
0
07/10/2019 08:30:00
Wednesday

EW1A-03-08
3
1
07/10/2019 08:30:00
Wednesday

EW1A-03-08
8
0
07/10/2019 10:30:00
Wednesday

EW1A-03-08
9
1
07/10/2019 10:30:00
Wednesday

EW1A-03-08
19
0
07/10/2019 15:30:00
Wednesday

EW1A-03-08
20
1
07/10/2019 15:30:00
Wednesday

room_code	rn	occupancy	room_time	day_from_date
EW1A-03-08	1	0	07/10/2019 08:00:00	Wednesday
EW1A-03-08	2	0	07/10/2019 08:30:00	Wednesday
EW1A-03-08	3	1	07/10/2019 08:30:00	Wednesday
EW1A-03-08	4	0	07/10/2019 09:00:00	Wednesday
EW1A-03-08	5	1	07/10/2019 09:00:00	Wednesday
EW1A-03-08	6	1	07/10/2019 09:30:00	Wednesday
EW1A-03-08	7	1	07/10/2019 10:00:00	Wednesday
EW1A-03-08	8	0	07/10/2019 10:30:00	Wednesday
EW1A-03-08	9	1	07/10/2019 10:30:00	Wednesday
EW1A-03-08	10	0	07/10/2019 11:00:00	Wednesday
EW1A-03-08	11	0	07/10/2019 11:30:00	Wednesday
EW1A-03-08	12	0	07/10/2019 12:00:00	Wednesday
EW1A-03-08	13	0	07/10/2019 12:30:00	Wednesday
EW1A-03-08	14	0	07/10/2019 13:00:00	Wednesday
EW1A-03-08	15	0	07/10/2019 13:30:00	Wednesday
EW1A-03-08	16	0	07/10/2019 14:00:00	Wednesday
EW1A-03-08	17	0	07/10/2019 14:30:00	Wednesday
EW1A-03-08	18	0	07/10/2019 15:00:00	Wednesday
EW1A-03-08	19	0	07/10/2019 15:30:00	Wednesday
EW1A-03-08	20	1	07/10/2019 15:30:00	Wednesday
EW1A-03-08	21	0	07/10/2019 16:00:00	Wednesday

room_code	rn	the_lag	occupancy	the_lead	the_2_lead	room_time	day_from_date
EW1A-03-08	1	null	0	0	1	07/10/2019 08:00:00	Wednesday
EW1A-03-08	2	0	0	1	0	07/10/2019 08:30:00	Wednesday
EW1A-03-08	3	0	1	0	1	07/10/2019 08:30:00	Wednesday
EW1A-03-08	4	1	0	1	1	07/10/2019 09:00:00	Wednesday
EW1A-03-08	5	0	1	1	1	07/10/2019 09:00:00	Wednesday
EW1A-03-08	6	1	1	1	0	07/10/2019 09:30:00	Wednesday
EW1A-03-08	7	1	1	0	1	07/10/2019 10:00:00	Wednesday
EW1A-03-08	8	1	0	1	0	07/10/2019 10:30:00	Wednesday
EW1A-03-08	9	0	1	0	0	07/10/2019 10:30:00	Wednesday
EW1A-03-08	10	1	0	0	0	07/10/2019 11:00:00	Wednesday
EW1A-03-08	11	0	0	0	0	07/10/2019 11:30:00	Wednesday
EW1A-03-08	12	0	0	0	0	07/10/2019 12:00:00	Wednesday
EW1A-03-08	13	0	0	0	0	07/10/2019 12:30:00	Wednesday
EW1A-03-08	14	0	0	0	0	07/10/2019 13:00:00	Wednesday
EW1A-03-08	15	0	0	0	0	07/10/2019 13:30:00	Wednesday
EW1A-03-08	16	0	0	0	0	07/10/2019 14:00:00	Wednesday
EW1A-03-08	17	0	0	0	0	07/10/2019 14:30:00	Wednesday
EW1A-03-08	18	0	0	0	1	07/10/2019 15:00:00	Wednesday
EW1A-03-08	19	0	0	1	0	07/10/2019 15:30:00	Wednesday
EW1A-03-08	20	0	1	0	null	07/10/2019 15:30:00	Wednesday
EW1A-03-08	21	1	0	null	null	07/10/2019 16:00:00	Wednesday