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 ofNULL
. For example, if yourBlockedUsers
table was like this:Then your query above would correctly filter the information.