Delete rows with overlapping times

Question

I'm trying to delete some conflicting date related to the times, this is a table of start times and stop times, and I want to write a query to delete overlapping data, in this case I would want to delete 13:00 row and 13:30 row(rows 6/7) because those times are already covered with the 12:00 to 14:00 row(rows 3/4). All the rows where VALUE_ENUM are 1 are start times and all the rows where VALUE_ENUM are 0 are stop times.

Here is an example of what the data in my table looks like:

SITE_ID ------- DEV_ID ------- SCHEDULE_TIME ------- VALUE_ENUM
---------------------------------------------------------------
1               3000           09:30:00              1
1               3000           11:30:00              0
1               3000           12:00:00              1
1               3000           14:00:00              0
1               3000           13:00:00              1
1               3000           13:30:00              0
1               3000           16:30:00              1
1               3000           18:30:00              0

If you ordered the query by schedule_time, would two sequential value_enum values of 1 indicated an overlap? — Philip, Sep 19 '11 at 19:27
How do you order your rows? I mean, how do you know that 14:00 comes before 13:00? — Mikael Eriksson, Sep 19 '11 at 20:15
They get inserted in order of start time so it would insert a start time and its corresponding end time, so it gets 12:00 and 14:00 at the same time and inserts one has the first value and then the 14:00. It gets all the times from a text file that is ordered by start time. — Beef, Sep 19 '11 at 20:30
You do realize that SQL is inherently un-ordered, right? You **cannot** rely on insert-order to sort your data - there is no garuantee that it's actually stored that way, much less that it's going to be _returned_ to you in that order. Unless you explicitly state a column to order by, the rows are returned in a _random_ order (for implementation reasons, this is _often_, but **NOT** garuanteed, in insertion order). If it's important, specify something that will be unique and ordered per insertion - like an `id` column... — Clockwork-Muse, Sep 19 '11 at 20:55
@Beef Your example of the data to be deleted is a special case of 'overlapping' where the time is _fully contained_ within the other. What about the scenario where only a portion of the other time interval overlaps - do you want to delete those too? And, if so, what is the criteria for deciding which one to delete? — Emmanuel, Sep 20 '11 at 16:02
no I do not want to delete those too, right now when time over laps I update the data in the database to extend the schedule to meet both times, so if it were one time from `8-10` and another from `9-12` I update the `10` to be `12` and ignore the `9` — Beef, Sep 20 '11 at 16:42

score 0 · Answer 1 · answered Sep 19 '11 at 19:03

0

It seems you want to delete a row when its schedule_time is less than the previous row. But how do your order the rows? Is there another column? Otherwise, once you delete the next insert will mostly likely be in that position and the next time you will end up deleting lot of rows wrongly.

Not sure how this can be done in a single sql statement but the procedural algorithm is straightforward -- initialize last_schedule_time to null and then on every row compare; delete if less or assign last_schedule_time to current.

answered Sep 19 '11 at 19:03

Miserable Variable

28,432
15
72
133

I actually have 2 questions posted about this the other goes into a lot more details so I posted a more simple version to see if I would get better help, here is the other version http://stackoverflow.com/questions/7474411/delete-rows-with-conflicting-times-using-jdbc-odbc – Beef Sep 19 '11 at 19:08
1

agree. from my side, I could say, all rows between 1st row and last row should be deleted, because they are overlapped. There must be some other criteria that he forgot mentioning. – Kent Sep 19 '11 at 19:11
they are not all over lapped though for this specific system it should be on from `9:30 to 11:30` then off and then back on from `12:00 to 14:00` and then off and then on again from `16:30 to 18:30` and then off again – Beef Sep 19 '11 at 19:33

score 0 · Accepted Answer · answered Sep 20 '11 at 17:33

Thanks for the help, but I was able to solve the answer by setting previous times for specific systems to temp variables and then did a comparison with the next times to see if they fell between the temp times, and if they did I just skipped the insert entirely, once again Thank you for the input and suggestions.

Delete rows with overlapping times

2 Answers2