clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1811948 fiddles created (25302 in the last week).

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) );
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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 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 07/10/2019 15:30:00 Wednesday
EW1A-03-08 21 1 0 07/10/2019 16:00:00 Wednesday
 hidden batch(es)


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
 hidden batch(es)