0

I have a program that downloads raw data from a sensor. Due to to some specifications made in the SQL code, there are values that are duplicated which needs to be deleted to maintain data integrity.

Below is a snapshot of the dataset: enter image description here

Do note that is is just a snapshot of july 10th. I have 33 other devices all of them have data dates from july 10th all the way through october.

Is there a way for me to traverse the whole dataset and find a way to set conditional statements. For example, if there are 2 values that share the same timing, take the row that has the "1" value.

At the same time, i would like to point out that this dataset is data that is collected 24/7.

3

You can use the ROW_NUMBER() function to produce a windowed record ID for each row, grouped by RoomCode and Time as per this db<>fiddle.

For each of the semi-duplicate rows (where RoomCode and Time are the same but Occupancy is different) this produces an incrementing number based on occupancy in descending order (so 1 = 1 and 0 = 2).

Place this in a CTE and then inner join your table to the CTE based on all columns except Occupancy where the ROW_NUMBER() pseudo column is > 1.

Example:

;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

Result:

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

You may need to massage this example and include additional columns in the PARTITION BY clause to ensure you're only selecting the correct near-duplicates but this should achieve the result you're after.

P.S. Its always a good idea in questions to include DDL (table definition), DML (INSERT statement with example data) and expected results to help clarify what you're trying to achieve.

1

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');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.