Select records where date not in table of multiple date ranges SQL Server

Question

I have a table in SQL Server which holds various date ranges.

Table has a start date and an end date as below:-

start_date                  end_date
=======================     =======================
2011-02-21 00:00:00.000     2011-02-25 00:00:00.000
2011-04-18 00:00:00.000     2011-04-29 00:00:00.000
2011-05-31 00:00:00.000     2011-06-03 00:00:00.000
2011-07-21 00:00:00.000     2011-08-31 00:00:00.000
2011-10-24 00:00:00.000     2011-10-28 00:00:00.000
2011-12-19 00:00:00.000     2012-01-02 00:00:00.000
2012-02-13 00:00:00.000     2012-02-17 00:00:00.000
2012-04-02 00:00:00.000     2012-04-13 00:00:00.000
2012-06-04 00:00:00.000     2012-06-08 00:00:00.000
2012-07-20 00:00:00.000     2012-08-31 00:00:00.000
2012-10-29 00:00:00.000     2012-11-02 00:00:00.000
2012-12-24 00:00:00.000     2013-01-01 00:00:00.000
2013-02-10 00:00:00.000     2013-02-16 00:00:00.000
2013-03-24 00:00:00.000     2013-04-06 00:00:00.000
2013-05-26 00:00:00.000     2013-06-01 00:00:00.000
2013-07-24 00:00:00.000     2013-08-31 00:00:00.000
2013-10-27 00:00:00.000     2013-11-02 00:00:00.000

I need to select records from another table where the date stamp does not exist in any of the ranges above.

A start date and end date may be the same in the table above, this indicates that only a single day is to be excluded.

My initial idea is to EXPAND all the dates from the ranges above, then do a SELECT WHERE NOT EXISTS IN, or is there a more efficient way of achieving this ?

Solution 1

SELECT t.* 
FROM AnotherTable t 
WHERE NOT EXISTS(
    SELECT 1 
    FROM TblDate d 
    WHERE d.start_date <= t.DateColumn
    AND   d.end_date   >= t.DateColumn
)

Edit: Just seen that this was your initial idea. So i assume that it's the simpliest and fastest approach. Here is something to read on this topic:

http://sqlserverperformance.idera.com/tsql-optimization/finding-nonexistent-records-hurry/

Solution 2

You could do a left join with the range table and add a where clause filtering out any matches.

SELECT anothertable.* FROM anothertable
LEFT JOIN range on anothertable.date >= range.start_date and anothertable.date <= range.end_date
WHERE range.start_date IS NULL

Solution 3

You need to check 2 Conditions: 1. Date is between the range 2. Date has the same Day like the start_date, but was later on the day than the end_date (you don't have hours, minutes and seconds in the range)

SELECT * FROM VALUE_TABLE
LEFT JOIN DATE_RANGE_TABLE ON 
  YEAR(VALUE_TABLE.CHECK_DATE) = YEAR(start_date)
  and MONTH (VALUE_TABLE.CHECK_DATE) = MONTH (start_date)
  AND DAY (VALUE_TABLE.CHECK_DATE) = DAY (start_date)
  OR VALUE_TABLE.CHECK_DATE BETWEEN start_date and end_date
WHERE start_date IS NULL

Solution 4

The below is more efficient than your proposal with (I think) the same end result. The only thing I am not sure about is if I have understood your criteria about the start date equalling the end date then excluding the entire day.

SELECT  *
FROM    Table1
        LEFT JOIN
        (   SELECT  Start_date, CASE WHEN End_Date = Start_Date THEN DATEADD(MILLISECOND, -1, DATEADD(DAY, 1, End_Date)) ELSE End_Date END [End_Date]
            FROM    table2
        ) Exc
            ON table.DateColumn BETWEEN Exc.Start_Date AND Exc.End_Date
WHERE   Exc.Start_Date IS NULL

View more solutions