Geek asks and answers

Add a case description in the Where section

I need to add a statement caseto the sentence where. I want it to run any of the following instructions depending on the value of TermDate.

Select * 
from myTable
where id = 12345
    AND TermDate CASE  
    WHEN NULL THEN
       AND getdate() BETWEEN StartDate AND DATEADD(dd, 30, StartDate)
    ELSE
    AND GETDATE < TermDate
    END
+3
source share
3 answers

Why not just use the condition OR?

SELECT * 
FROM  myTable
WHEN  id = 12345
AND   ((TermDate IS NULL AND 
        getdate() BETWEEN StartDate AND DATEADD(dd, 30, StartDate)) OR
       GETDATE() < TermDate)
+7
source

Since we all posted three exact answers, obviously too much, here is a version that uses your design case when.

use this:

select * 
from myTable
where id = 12345
AND   case
      when TermDate IS NULL
           AND getdate() BETWEEN StartDate AND DATEADD(dd, 30, StartDate)
      then 1
      when GETDATE < TermDate
      then 1
      else 0
      end
      = 1
+2
source

You can accomplish this using ANDsand ORs. Try the following query.

Select * 
From myTable
where id = 12345
AND ((TermDate IS NULL 
          AND GETDATE() BETWEEN StartDate AND DATEADD(dd, 30, StartDate)) 
    OR (GETDATE() < TermDate))
+2
source

More articles:


All Articles