4

I'm trying to retrieve the records from the joined tables based on 2 condition like below:

Where if(b.enddate is null)
         a.starttime >= b.starttime
      else
         a.starttime >= b.starttime and a.endtime <= b.endtime

I have seen examples using case when but the result is not what I wanted. Please assist me to convert this condition into a proper sql format.

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
Gary
  • 55
  • 1
  • 7
  • It's also possible to use boolean negated-parity conditions, like: `.. (b.enddate is null /* positive */ and (..)) or (b.enddate is not null /* negative */ and (..))`. – user2864740 Jun 22 '18 at 03:15
  • I suggest that you use the solution giveb by @Susang. But If you just have a XY-Problem and you just want to know how to 'IF' in 'WHERE' then you should look at this question https://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause – Horitsu Jun 22 '18 at 05:06

5 Answers5

6

You don't need to use case expression, this can be done using OR in WHERE clause

... Where 
( b.enddate is null and a.starttime >= b.starttime)
or     
( b.enddate is not null and a.starttime >= b.starttime and a.endtime <= b.endtime)
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
3

I think we can simply do this in short because in both the cases a.starttime >= b.starttime is applicable so we just need to handle a.endtime <= b.endtime condition in case of b.enddate is NULL or NOT NULL so I do as below: 

WHERE a.starttime >= b.starttime and (b.enddate is null OR a.endtime <= b.endtime)
Shushil Bohara
  • 5,556
  • 2
  • 15
  • 32
  • 1
    This is the solution to prefer, because it just uses boolean algebra to get rid of a double term and provides the most effective formular. – Horitsu Jun 22 '18 at 04:55
2

What about: 

   WHERE (b.enddate is null AND a.starttime >= b.starttime) OR (b.enddate is not null AND a.starttime >= b.starttime and a.endtime <= b.endtime)
Erick Lanford Xenes
  • 1,467
  • 2
  • 20
  • 34
2

for simplicity sake, you can try below sample conditional logic:

DECLARE @enddate datetime2(7);
SET @enddate = NULL;

SELECT @enddate;

IF @enddate IS NULL
    SELECT 1, 2 FROM dbo.table_a as a
    INNER JOIN dbo.table_b as b ON a.id = b.id
    WHERE a.starttime >= b.starttime
ELSE
    SELECT 1, 2 FROM dbo.table_a as a
    INNER JOIN dbo.table_b as b ON a.id = b.id
    WHERE a.starttime >= b.starttime and a.endtime <= b.endtime

this gives you the benefit of 2 separate execution plan. So if you decide to optimize your SQL statements, it would be much easier (like indexing, plan stability, move to sp, etc).

1

Try this sir, 

   where (b.enddate is null and a.starttime >= b.starttime) OR (a.starttime >= b.starttime and a.endtime <= b.endtime)

Thanks :)

Genebert
  • 70
  • 11