Sql-server – Get Data for user excluding another user blocked with its time interval

sql serversql-server-2012

Two tables

Message



  ID | Message | FromUserId | ToUserId  | DateCreated
    1  | M1      |     A      |   B       | 2017-10-05 12:00:00
    2  | M2      |     A      |   B       | 2017-10-05 12:05:00
    3  | M3      |     B      |   A       | 2017-10-06 12:05:00
    4  | M4      |     B      |   A       | 2017-10-06 12:05:00
    5  | M5      |     A      |   B       | 2017-10-07 12:05:00
    6  | M6      |     A      |   B       | 2017-10-07 12:05:00
    7  | M7      |     B      |   A       | 2017-10-08 12:05:00
    8  | M8      |     B      |   A       | 2017-10-08 12:15:00
    9  | M9      |     B      |   A       | 2017-10-08 12:18:00
   10  | M9      |     B      |   A       | 2017-10-09 12:05:00

BlockedUsers

ID | FromUserId | BlockUserId | StartBlockDate       | EndBlockDate
1  |    A       |   B         | 2017-10-06 11:55:00  | 2017-10-07 11:55:00 
2  |    A       |   B         | 2017-10-08 10:05:00  | 2017-10-08 12:07:00 
3  |    A       |   B         | 2017-10-09 11:55:00  | NULL 

Now when B tries to retrieve messages it gets whole messages of Message table

but when A retrieves messages, its result should be

Message

ID | Message | FromUserId | ToUserId  | DateCreated
1  | M1      |     A      |   B       | 2017-10-05 12:00:00
2  | M2      |     A      |   B       | 2017-10-05 12:05:00
5  | M5      |     A      |   B       | 2017-10-07 12:05:00
6  | M6      |     A      |   B       | 2017-10-07 12:05:00
8  | M8      |     B      |   A       | 2017-10-08 12:15:00
9  | M9      |     B      |   A       | 2017-10-08 12:18:00

I tried with below query

Declare @UserId int = A 
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
    and bu.FromUserId = @UserId
  )
;

I am stuck with the condition for user being blocked and another user.

I took reference of question:get data excluding time intervals

but could not figure

Best Answer

The best way to handle this would be to make the default value for the EndBlockDate be an arbitrary date far in the future instead of NULL. For example, if your BlockedUsers table was like this:

BlockedUsers

ID | FromUserId | BlockUserId | StartBlockDate       | EndBlockDate
1  |    A       |   B         | 2017-10-06 11:55:00  | 2017-10-07 11:55:00 
2  |    A       |   B         | 2017-10-08 10:05:00  | 2017-10-08 12:07:00 
3  |    A       |   B         | 2017-10-09 11:55:00  | 2150-12-31 23:59:59

Then your query above would correctly filter the information.