Search code examples
sql-serverdatetimesql-server-2014sql-delete

Delete Rows in SQL Table Based on Difference Between Values in Another Table

I have one table (let's call it dbo.temp) that has minute by minute record of temperature from different sensors:

+-----------+------------+----------+------+
| SENSOR_ID |   DATE     |   TIME   | TEMP |
+-----------+------------+----------+------+
|    106971 | 2017-01-10 | 10:05:00 | 21.1 |
|    106971 | 2017-01-10 | 10:06:00 | 21.1 |
|    106971 | 2017-01-10 | 10:07:00 | 21.2 |
|    106971 | 2017-01-10 | 10:08:00 | 21.1 |
|    106971 | 2017-01-10 | 10:09:00 | 21.1 |
|    106971 | 2017-01-10 | 10:10:00 | 21.2 |
|    106971 | 2017-01-10 | 10:11:00 | 21.2 |
|    106971 | 2017-01-10 | 10:12:00 | 21.2 |
|    106971 | 2017-01-10 | 10:13:00 | 21.3 |
|    106971 | 2017-01-10 | 10:14:00 | 21.4 |
|    106971 | 2017-01-10 | 10:15:00 | 21.4 |
|    106971 | 2017-01-10 | 10:16:00 | 21.4 |
|    106971 | 2017-01-10 | 10:17:00 | 21.5 |
|    106971 | 2017-01-10 | 10:18:00 | 21.3 |
|    106971 | 2017-01-10 | 10:19:00 | 21.3 |
|    106971 | 2017-01-10 | 10:20:00 | 21.3 |
|    106971 | 2017-01-10 | 10:21:00 | 21.4 |
+-----------+------------+----------+------+

And a second (dbo.datetime) contains date and time values. 

+-------------+-----------+----------+
|    DATE     | TIME_A    | TIME_B  |
+-------------+-----------+----------+
| 2017-01-10  | 10:13:00  | 10:18:00 |
| 2017-01-11  | 09:18:00  | 10:28:00 |
| 2017-01-12  | 10:45:00  | 10:59:00 |
+-------------+-----------+----------+

I want to delete rows in dbo.temp where the time is between the two given in dbo.datetime on any given date. SENSOR_ID is not important as all data between TIME_A and TIME_B needs to be deleted.

For example I want dbo.temp to become this after the deletion between TIME_A and TIME_B on 2017-01-10:

+-----------+------------+----------+------+
| SENSOR_ID |   DATE     |   TIME   | TEMP |
+-----------+------------+----------+------+
|    106971 | 2017-01-10 | 10:05:00 | 21.1 |
|    106971 | 2017-01-10 | 10:06:00 | 21.1 |
|    106971 | 2017-01-10 | 10:07:00 | 21.2 |
|    106971 | 2017-01-10 | 10:08:00 | 21.1 |
|    106971 | 2017-01-10 | 10:09:00 | 21.1 |
|    106971 | 2017-01-10 | 10:10:00 | 21.2 |
|    106971 | 2017-01-10 | 10:11:00 | 21.2 |
|    106971 | 2017-01-10 | 10:12:00 | 21.2 |
|    106971 | 2017-01-10 | 10:19:00 | 21.3 |
|    106971 | 2017-01-10 | 10:20:00 | 21.3 |
|    106971 | 2017-01-10 | 10:21:00 | 21.4 |
+-----------+------------+----------+------+

How do I do this? dbo.datetime has daily TIME_A and TIME_B values for ~14 months, so don't want to manually delete from dbo.temp where date = 'X' and time between 'Y' and 'Z' each day.

Solution

  • Not quite sure if I understand you question, but you can try this query

    DELETE t
FROM dbo.temp t
JOIN dbo.datetime d ON d.Date = t.Date
WHERE t.Time BETWEEN d.Time_A AND Time_B