SQL how to include conditional in where clause

Question

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?

Can you show all of the code? You might be better off using dynamic SQL. — SS_DBA
– SS_DBA, Commented Jan 20, 2017 at 20:42
Similar question to consider: stackoverflow.com/questions/2568775/… — Fritz
– Fritz, Commented Jan 20, 2017 at 20:46
Not an answer to your question, more of a comment on coding style. sqlblog.org/2011/09/20/… — Sean Lange
– Sean Lange, Commented Jan 20, 2017 at 21:47

Drew Leffelman · Accepted Answer · 2017-01-20 20:44:27Z

3

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 ) )

answered Jan 20, 2017 at 20:44

Drew Leffelman

5263 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hatchet - done with SOverflow · Accepted Answer · 2017-01-20 22:10:04Z

0

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)

answered Jan 20, 2017 at 21:43

16.3k5 gold badges49 silver badges58 bronze badges

marc_s · Accepted Answer · 2017-01-22 15:52:48Z

0

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

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 21, 2017 at 5:21

Quan Le

113 bronze badges