CODEWITHSUNDEEP
sql
Stefanos Karapistolis
Stefanos Karapistolis

Reputation: 11

is there a way to use an if statement in the WHERE part of the SQL query?

Is there a way to use an if statement in the where part of the SQL query?
For example: 

 SELECT count(*) 
    from table_name tb 
    where ( if (@enddate>dateadd("d",2,@date) then date > tb.date 
    else dateadd("d",2,@date)>tb.date) )

I need to somehow do this check where I check if the date 2 days later is not greater than the end date, otherwise I have to use the end date by default.

Upvotes: 0

Views: 100

Answers (3)

Stefanos Karapistolis
Stefanos Karapistolis

Reputation: 11

I found the following: 

CASE
 WHEN (DATENAME(dw,@st) = 'Monday')
       AND (
        ((Day(@st) = 01))
        OR ((Day(@st) = 02))
        OR ((Day(@st) = 03))
       )
  THEN DATEADD(DAY,-2,@st) 
 ELSE DATEADD(DAY, - 1, DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
END

And it actually works quite nicely.

This will only run when the first working day of the month is a Monday, hence that's when I want to get the weekend's data. Otherwise, I already have it.

Upvotes: 0

Paolo
Paolo

Reputation: 2254

this answer is tailored onto to the case explained into the question as the issue to solve and not on the title.

to handle that case there is no need for a special solution and/or keyword, it is handled nicely using a regular WHERE clause:

 SELECT count(*) 
 from table_name tb 
 where (@enddate > dateadd("d",2,@date) AND date > tb.date)
       OR
       (dateadd("d",2,@date) > tb.date))

Upvotes: 1

Guneli
Guneli

Reputation: 1731

You can use CASE expression:

SELECT count(*) 
FROM table_name tb 
WHERE (CASE WHEN (@enddate>dateadd("d",2,@date)) THEN date > tb.date 
       ELSE
          dateadd("d",2,@date)>tb.date
       END);

Upvotes: 4

Related Questions