TL;DR this doesn't answer the question as asked, but rather picks out the two row combinations that are highlighted - which essentially boils down to selecting out islands of 0 - 1 - 0 of occupancy and not runs of, say 0 - 1 - 1 - 0 or more.
This was tricky and requires the use of the LAG() AND LEAD() and the ROW_NUMBER() functions. Your requirement is made difficult by virtue of the fact that apparently you do not want runs of occupancy = 1, but rather occupancy of 0 followed by 1 followed by 0.
The table, data and query are in the fiddle here (and at the end of this answer), but I'll explain my reasoning.
The first query to run is this one:
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
Results look like this (snipped for brevity):
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
...
...
Note the use of the offset parameter (LEAD(d.occupancy, 2)) to the LEAD() function. This allows me to check two days ahead - helping to check the existence of runs of occupancy = 1.
Then, I ran this query - note the very handy chaining of CTEs (Common Table Expressions, otherwise known as the WITH clause):
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)
)
Result:
room_code rn the_lag occupancy the_lead the_2_lead room_time day_from_date
EW1A-03-08 2 0 0 1 0 07/10/2019 08:30:00 Wednesday
EW1A-03-08 8 1 0 1 0 07/10/2019 10:30:00 Wednesday
EW1A-03-08 19 0 0 1 0 07/10/2019 15:30:00 Wednesday
This gives me the desired slots from the original table with occupancy = 0, i.e. rows numbered 2, 8 and 19.
Then, I ran:
cte3 AS
(
SELECT c2.rn AS rn2, c1.rn AS rn1
FROM cte2 c2
JOIN cte1 c1
ON c2.rn + 1 = c1.rn
)
Result:
rn2 rn1
2 3
8 9
19 20
Which gives me the row numbers of the desired occupancy = 0 slots plus the row numbers of the desired occupancy = 1 slots from the original table.
So, finally, I ran a UNION query using both row_number columns from my simple table above:
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
Giving the final result:
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
6 rows
Which looks like the correct answer from the image you posted. If this does not meet your requirements, let me know. I'm afraid that I don't know how efficient it will be on a large dataset. With suitable indexes and once you've pulled out the desired slots of occupancy = 0, the amount of records to be pulled out mightn't be too large - YMMV, this will depend on your data.
p.s. welcome to the forum! :-)
================= Full SQL, table and data ===========================
Thanks to @HandyD for the original fiddle with table and data. As he mentions, in future you should include your tables and data (suitably anonymised) as DDL and DML either in your question or in a fiddle. There are some articles on how to answer questions here on my profile - you might like to take a look? Finally, screenshots are discouraged on StackExchange/StackOverflow.
SQL:
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
Table:
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)
);
Data:
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');
