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 (25310 in the last week).

CREATE TABLE DataPoints ( RoomCode VARCHAR(25), Occupancy INT, [Time] DATETIME, DayFromDate VARCHAR(15) )
 hidden batch(es)


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


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


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


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