CODEWITHSUNDEEP
sql-server-2008casewhere-clause
Reverend Bubbles
Reverend Bubbles

Reputation: 1393

Using CASE statements in WHERE clause

I've looked at a lot of similar questions asked on here and other places and I don't know how to extrapolate what they're saying to what I need, so I opted to ask for my scenario.

Basically, I need to check that the date of a row is before a certain date. The date, (this predates me and there's nothing I can do about it despite not liking the system) is stored simply as a single number which corresponds to the number of the days into the year it is. For example, Jan 1 is 1, Feb 1st is 32, etc...So, the date is figured out by adding that number to 12/31/. The problem I'm running into is that when I'm checking a period that spans New Year's, it's bringing back (for example) December 29 of THIS year, instead of LAST year. I ran into this in the SELECT and the CASE statement worked fine. In the WHERE clause, however, it's not working as expected. In the where clause, I need to check to make sure it's not a weekend. (Again, code predates me and I can't change much, sadly)

So, I now have this in the WHERE clause:

CASE 
    WHEN (dateadd(dd, t.periodid, '12/31/2013') > '01/24/2014') THEN AND (DATEPART(DW, convert(varchar, dateadd(dd, t.periodid, '12/31/2012'), 107)) NOT IN (1,7) OR t.periodid IS NULL) 
    WHEN (dateadd(dd, t.periodid, '12/31/2013') <= '01/24/2014') THEN AND (DATEPART(DW, convert(varchar, dateadd(dd, t.periodid, '12/31/2013'), 107)) NOT IN(1,7) OR t.periodid IS NULL)    
END

(i.e.: If the date is greater than the last day of the period, add the number to two years ago and check if it's a weekend. Otherwise, add it to 12/31 of LAST year and check if it's a weekend.)

I think that I get that it's failing because my case should be something more like:

AND X = CASE.....

but I have no idea what to put as X in this situation.

Upvotes: 8

Views: 45503

Answers (2)

Reverend Bubbles
Reverend Bubbles

Reputation: 1393

Thanks all. I actually finally figured this out, right before I came back to check out more answers.

I ended up doing this:

    AND 1 = 
    CASE WHEN (dateadd(dd, t.periodid, '12/31/2013') > '01/24/2014') AND (DATEPART(DW, convert(varchar, dateadd(dd, t.periodid, '12/31/2012'), 107)) NOT IN(1,7) OR t.periodid IS NULL)
    THEN 1 
    WHEN (dateadd(dd, t.periodid, '12/31/2013') <= '01/24/2014') AND (DATEPART(DW, convert(varchar, dateadd(dd, t.periodid, '12/31/2013'), 107)) NOT IN(1,7) OR t.periodid IS NULL)
    THEN 1
    END

which seems to work!

Upvotes: 5

Becuzz
Becuzz

Reputation: 6866

Your where clause should look something like this if you want to use a case statement inside of it:

WHERE 
(CASE 
    WHEN (dateadd(dd, t.periodid, '12/31/2013') > '01/24/2014') AND (DATEPART(DW, convert(varchar, dateadd(dd, t.periodid, '12/31/2012'), 107)) NOT IN (1,7) OR t.periodid IS NULL) THEN 1
    WHEN (dateadd(dd, t.periodid, '12/31/2013') <= '01/24/2014') AND (DATEPART(DW, convert(varchar, dateadd(dd, t.periodid, '12/31/2013'), 107)) NOT IN(1,7) OR t.periodid IS NULL) THEN 1
    ELSE 0   
END) = 1

Upvotes: 8

Related Questions