Get Data Excluding Time Intervals in Another Table in SQL Server

sql servert-sql

I have two tables, Message and BlockUser.

Table Message:

ID | Message | DateCreated 
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00 
3  | M3      | 2017-09-21 13:00:00
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

Table BlockUser:

BlockStartDate       | BlockEndDate
2017-09-21 13:00:00  | 2017-09-22 13:00:00
2017-09-24 13:00:00  | 2017-09-24 14:00:00

Now I want rows from Message excluding those matching the intervals defined in BlockUser. So for my example the result should be:

ID | Message | DateCreated 
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00 
5  | M3      | 2017-09-23 13:00:00
7  | M3      | 2017-09-25 13:00:00

I tried using this query but it didn't work:

WITH CTE AS (
         SELECT BlockStartDate,BlockEndDate FROM BlockUser
         )
SELECT * FROM Message 
WHERE DateCreated NOT BETWEEN 
(SELECT BlockStartDate FROM CTE) 
AND 
(SELECT BlockEndDate  FROM CTE)

It gives me this error:

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

Best Answer

This is a classic anti-join scenario. You want rows from one table excluding those matching another table.

In English, you could put the condition like this:

Select every row from Message for which there is no matching row in BlockUser.

In SQL, one way of expressing the above is like this:

SELECT
  *
FROM
  dbo.Message AS m
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      dbo.BlockUser AS bu
    WHERE
      m.DateCreated >= bu.BlockStartDate
      AND m.DateCreated < bu.BlockEndDate
  )
;
Related Question