delete records in time ranges in SQL Server

I have a lot of time ranges in TimeRanges table:

StartTime         EndTime
2010-01-01 06:00  2010-01-01 18:00
2010-01-01 20:00  2010-01-01 22:00
2010-01-02 06:00  2010-01-02 18:00
2010-01-02 20:00  2010-01-02 22:00
2010-01-03 06:00  2010-01-03 18:00
2010-01-03 20:00  2010-01-03 22:00
2010-01-04 06:00  2010-01-04 18:00
2010-01-04 20:00  2010-01-04 22:00
...
...
2010-02-01 06:00  2010-02-01 18:00
2010-02-01 20:00  2010-02-01 22:00
2010-02-02 06:00  2010-02-02 18:00
2010-02-02 20:00  2010-02-02 22:00
2010-02-03 06:00  2010-02-03 18:00
2010-02-03 20:00  2010-02-03 22:00
2010-02-04 06:00  2010-02-04 18:00
2010-02-04 20:00  2010-02-04 22:00
...
...
2011-01-01 06:00  2011-01-01 18:00
2011-01-01 20:00  2011-01-01 22:00
2011-01-02 06:00  2011-01-02 18:00
2011-01-02 20:00  2011-01-02 22:00
2011-01-03 06:00  2011-01-03 18:00
2011-01-03 20:00  2011-01-03 22:00
2011-01-04 06:00  2011-01-04 18:00
2011-01-04 20:00  2011-01-04 22:00
...
...
2011-02-01 06:00  2011-02-01 18:00
2011-02-01 20:00  2011-02-01 22:00
2011-02-02 06:00  2011-02-02 18:00
2011-02-02 20:00  2011-02-02 22:00
2011-02-03 06:00  2011-02-03 18:00
2011-02-03 20:00  2011-02-03 22:00
2011-02-04 06:00  2011-02-04 18:00
2011-02-04 20:00  2011-02-04 22:00

and I have some filters in TimeFilters table:

StartTime         EndTime
2010-02-01 00:00  2010-03-01 00:00
2011-02-01 00:00  2011-03-01 0开发者_如何转开发0:00
2012-02-01 00:00  2012-03-01 00:00

What I need is to delete the records from TimeRanges table, only those time ranges that are within the TimeFilters table will be retained.

To simply put, I want the following records retained: from 2010-02-01 00:00 to 2010-03-01 00:00, like:

2010-02-01 06:00  2010-02-01 18:00
2010-02-01 20:00  2010-02-01 22:00
2010-02-02 06:00  2010-02-02 18:00
2010-02-02 20:00  2010-02-02 22:00
2010-02-03 06:00  2010-02-03 18:00
2010-02-03 20:00  2010-02-03 22:00
2010-02-04 06:00  2010-02-04 18:00
2010-02-04 20:00  2010-02-04 22:00

from 2011-02-01 00:00 to 2011-03-01 00:00, like:

2011-02-01 06:00  2011-02-01 18:00
2011-02-01 20:00  2011-02-01 22:00
2011-02-02 06:00  2011-02-02 18:00
2011-02-02 20:00  2011-02-02 22:00
2011-02-03 06:00  2011-02-03 18:00
2011-02-03 20:00  2011-02-03 22:00
2011-02-04 06:00  2011-02-04 18:00
2011-02-04 20:00  2011-02-04 22:00

What I can do now is to select those records are within TimeFilters into a temporary table, then truncate the TimeRanges table, and put back records from the temporary table, but that's quite time consuming.

To delete all the records in TimeRanges that do not have a match in TimeFilters, you can do

delete TimeRanges
  from TimeRanges r
  left join timefilters f on r.StartTime >= f.StartTime and r.EndTime <= f.EndTime 
 where f.StartTime is null

The tricky thing here is to do a left join, and delete those TimeRanges that do not have a correspondent filter (when f.startTime or f.endTime are null)

After deleting, you can see the results:

select *
  from TimeRanges

StartTime               EndTime
----------------------- -----------------------
2010-02-01 06:00:00.000 2010-02-01 18:00:00.000
2010-02-01 20:00:00.000 2010-02-01 22:00:00.000
2010-02-02 06:00:00.000 2010-02-02 18:00:00.000
2010-02-02 20:00:00.000 2010-02-02 22:00:00.000
2010-02-03 06:00:00.000 2010-02-03 18:00:00.000
2010-02-03 20:00:00.000 2010-02-03 22:00:00.000
2010-02-04 06:00:00.000 2010-02-04 18:00:00.000
2010-02-04 20:00:00.000 2010-02-04 22:00:00.000
2011-02-01 06:00:00.000 2011-02-01 18:00:00.000
2011-02-01 20:00:00.000 2011-02-01 22:00:00.000
2011-02-02 06:00:00.000 2011-02-02 18:00:00.000
2011-02-02 20:00:00.000 2011-02-02 22:00:00.000
2011-02-03 06:00:00.000 2011-02-03 18:00:00.000
2011-02-03 20:00:00.000 2011-02-03 22:00:00.000
2011-02-04 06:00:00.000 2011-02-04 18:00:00.000
2011-02-04 20:00:00.000 2011-02-04 22:00:00.000

(16 row(s) affected)

What you can do is an outer join to get both the matches and the non-matches and then have a condition to extract the non-matches:

select * form TimeRanges r join TimeFilters f on
    r.StartTime between f.StartTime and f.EndTime and
    r.EndTime between f.StartTime and f.EndTime
where f.StartTime is null and f.EndTime is null

I don't know if the rest can be done with MySQL, but what you'll want to do is generate a condition that matches each each row that didn't fall between the dates in the filter table. The pseudo code to do this would be as follows:

cond='';

while (r = read row) do
    if (cond=='') {
        sep='';
    } else {
        sep=' OR ';
    }

    cond = cond . sep .
        '(r.StartDate=' . r->StartDate . ' and r.EndDate=' . r->EndDate . ')';
}

# Before running the delete query comment it out and run the query printed by:
#  print 'select * from TimeRanges where '.cond;
run query 'delete from TimeRanges where '.cond;

If this can't be done using MySql, then it can be done using a scripting language, such as PHP.

I assume you have some sort of ID column on your TimeRanges table? If so, would something like this work for you?

DELETE
    TimeRanges
WHERE
    id NOT IN ( SELECT
                    tr.id
                FROM
                    TimeRanges tr
                    JOIN TimeFilter tf ON tr.startdate >= tf.startdate AND
                                           tr.enddate <= tf.enddate )