CODEWITHSUNDEEP
sqlsql-server
VAAA
VAAA

Reputation: 15039

SQL how to include conditional in where clause

I have a SQL Server query that filters some data using:

and datepart(mm,cs.timestamp) <= @month

I have a parameter @accumulate that if true then I want to to above logic but if false then I want to do:

and datepart(mm,cs.timestamp) = @month

Any clue on how to that in same line?

Upvotes: 0

Views: 173

Answers (3)

Quan Le
Quan Le

Reputation: 11

You should use stored procedure with IF and THEN statement to separate the two conditions

Upvotes: 0

hatchet - done with SOverflow
hatchet - done with SOverflow

Reputation: 16259

This is a little more concise, that could be one line

(DATEPART(mm,cs.timestamp) = @month 
 OR (@accumulate = 1 AND DATEPART(mm, cs.timestamp) < @month))

or, with a nod to Sean's comment (which is good advice)

(DATEPART(MONTH,cs.timestamp) = @month 
 OR (@accumulate = 1 AND DATEPART(MONTH, cs.timestamp) < @month))

or, if you want a short but opaque and nasty bit of code (and @accumulate is a bit)

SIGN(@month - DATEPART(MONTH, cs.timestamp)) in (0, @accumulate)

Upvotes: 0

Drew Leffelman
Drew Leffelman

Reputation: 526

I'm not sure if you can do it in one line. But you could do it in multiple lines like:

and ( ( datepart(mm,cs.timestamp) <= @month and @accumulate = true )
   or ( datepart(mm.cs.timestamp) = @month and @accummulate = false ) )

Upvotes: 3

Related Questions