CODEWITHSUNDEEP
sql-server
DDemirci
DDemirci

Reputation: 5

Null check and comparing in then clause

I have two tables in my database. First one is called F and the second one is T. F stores two information. "date_received" and "date_removed" that can be null. T stores only "date" that is an arbitrary date. F can store information for an object. For instance,

2019-05-19 2019-05-31 ==> Means it belonged to someone between these dates

2019-06-03 NULL ==> Means it still belongs to someone.

I have two dates in T , 2019-05-25 and 2019-06-05. It is easy to find for 2019-05-25 because I can use BETWEEN clause but 2019-06-05 I cannot. I need to write if-else or case condition to check that if F.date_removed is null or not

CASE
  WHEN F.date_removed IS NULL THEN T.date > F.date_received
  ELSE  T.date BETWEEN F.date_received AND F.date_removed
END

How can I handle this issue. I need to switch between two comparing options according to being null of "date_removed"

Upvotes: 0

Views: 158

Answers (2)

DBro
DBro

Reputation: 432

Another option, which is slightly less wordy.

WHERE
        T.date >= F.date_received
    AND (F.date_removed IS NULL OR T.date <= F.date_removed)

Upvotes: 1

Eliseu Marcos
Eliseu Marcos

Reputation: 311

From what I understand this your rule will stay in the where clause, so you can do this way.

WHERE
((F.date_removed IS NULL AND T.date > F.date_received) OR (T.date BETWEEN F.date_received AND F.date_removed))

Upvotes: 2

Related Questions