SQL Server 2017

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

CREATE TABLE DataPoints
(
  RoomCode VARCHAR(25),
  Occupancy INT,
  [Time] DATETIME,
  DayFromDate VARCHAR(15)
)
INSERT INTO DataPoints (RoomCode, Occupancy, Time, DayFromDate)
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 * FROM DataPoints


RoomCode
Occupancy
Time
DayFromDate


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


;WITH DataCTE AS (
SELECT RoomCode, Occupancy, Time, DayFromDate, 
ROW_NUMBER() OVER (PARTITION BY RoomCode, Time ORDER BY Occupancy DESC) AS RN
FROM DataPoints
)

DELETE d1
FROM DataPoints d1
INNER JOIN DataCTE d2
ON d1.RoomCode = d2.RoomCode
 AND d1.Occupancy = d2.Occupancy
 AND d1.Time = d2.Time
 AND d1.DayFromDate = d2.DayFromDate
WHERE d2.RN > 1
4 rows affected

SELECT * FROM DataPoints


RoomCode
Occupancy
Time
DayFromDate


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

RoomCode	Occupancy	Time	DayFromDate
EW1A-03-08	0	07/10/2019 08:00:00	Wednesday
EW1A-03-08	0	07/10/2019 08:30:00	Wednesday
EW1A-03-08	1	07/10/2019 08:30:00	Wednesday
EW1A-03-08	0	07/10/2019 09:00:00	Wednesday
EW1A-03-08	1	07/10/2019 09:00:00	Wednesday
EW1A-03-08	1	07/10/2019 09:30:00	Wednesday
EW1A-03-08	1	07/10/2019 10:00:00	Wednesday
EW1A-03-08	0	07/10/2019 10:30:00	Wednesday
EW1A-03-08	1	07/10/2019 10:30:00	Wednesday
EW1A-03-08	0	07/10/2019 11:00:00	Wednesday
EW1A-03-08	0	07/10/2019 11:30:00	Wednesday
EW1A-03-08	0	07/10/2019 12:00:00	Wednesday
EW1A-03-08	0	07/10/2019 12:30:00	Wednesday
EW1A-03-08	0	07/10/2019 13:00:00	Wednesday
EW1A-03-08	0	07/10/2019 13:30:00	Wednesday
EW1A-03-08	0	07/10/2019 14:00:00	Wednesday
EW1A-03-08	0	07/10/2019 14:30:00	Wednesday
EW1A-03-08	0	07/10/2019 15:00:00	Wednesday
EW1A-03-08	0	07/10/2019 15:30:00	Wednesday
EW1A-03-08	1	07/10/2019 15:30:00	Wednesday
EW1A-03-08	0	07/10/2019 16:00:00	Wednesday