CASE WHEN in WHERE Statement

Question

I'm very new to SQL and am having trouble with a Where Statement. I'm trying to look at Checks from Yesterday and a week from yesterday, unless today (Today being the day the query is run) is a Monday, then I want to look at Friday and the friday Prior. The Error message is "Expected something between the word 'Check_date' and '='.

WHERE
    CASE WHEN DAYOFWEEK(date) = 2 
        THEN (a.check_date = DATE - 3 OR a.check_date = DATE - 10)
        ELSE (a.check_date = DATE - 1 OR a.check_date = DATE - 8) END

Solution

In T-SQL, CASE is an expression that returns a single value from one of the branches. It is not a statement, and cannot be used for control of flow like it can in other languages. You cannot put the entire conditional inside. The semantic difference may seem minor, but the behavioral difference is significant.

To use a CASE expression in this case (no pun intended) would not be very valuable, since it will require multiple branches, e.g. (and I'm just guessing here since your current query isn't really clear at what you are trying to do):

WHERE a.checkdate IN 
  (DATEADD(DAY, 0-CASE DATEPART(WEEKDAY, [DATE]) 
    WHEN 2 THEN 3  ELSE 1 END, [DATE]),
  (DATEADD(DAY, 0-CASE DATEPART(WEEKDAY, [DATE]) 
    WHEN 2 THEN 10 ELSE 8 END, [DATE]);

Of course it's much easier to combine these without introducing a CASE expression for no reason:

WHERE 
  (
    DATEPART(WEEKDAY, [DATE]) = 2
    AND a.checkdate IN (DATEADD(DAY, -3, [DATE]), DATEADD(DAY, -10, [DATE]))
  )
  OR
  (
    DATEPART(WEEKDAY, [DATE]) <> 2
    AND a.checkdate IN (DATEADD(DAY, -1, [DATE]), DATEADD(DAY, -8,  [DATE]))
  );

Even easier would be having two variables defined up front (though I'm not sure how teradata works with batches):

 DECLARE @d1 DATE, @d2 DATE;

 SET @d1 = DATEADD(DAY, -1, [DATE]);
 SET @d2 = DATEADD(DAY, -8, [DATE]);

 IF DATEPART(WEEKDAY, [DATE]) = 2
 BEGIN
   SELECT @d1 = DATEADD(DAY, 2, @d1), @d2 = DATEADD(DAY, 2, @d2);
 END

 SELECT ...
 WHERE a.checkdate IN (@d1, @d2);