title: SQL Case Statement in Where Clause
body: <p>I have a table with accounting_period in the form of 201101 for Jan 2011, 201102 for Feb 2011, etc.
  <br>
  I am trying to sum a column (eff_cc) for the quarter. That is, I want to get the sum the data for Jan, Feb &amp; Mar 2011 for a date in the 1st quarter, etc.</p>
  
  <p>So, I used a Case statement in the where clause.  Basically I said (in the where clause) that:</p>
  
  <ul>
  <li>if the current month is 1,4,7 or 10, then get data from that month; </li>
  <li>if the current month is 2,5,8 or 11, then get the data from the curent month &amp; the previous month; and </li>
  <li>if the current month is 3,6,9, or 12 then get data from the current and the previous two months</li>
  </ul>
  
  <p>Doesn't want to work.  The code is below.</p>
  
  <pre><code>select phase_code, accounting_period, sum(eff_cc) as BD_Eff_QTD, 
  from prj_detail
  where 
      case month(getdate()) % 3
          when  1 then    -- current month is 1,4,7,10
              accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2)
          when 2 then     -- current month is 2, 5, 8, 11
              (accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2) or 
              accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-1),2))
          when 3 then     -- current month is 3, 6, 9, 12
              (accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())),2) or 
              accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-1),2) or
              accounting_period = right(Year(getDate()),4) + Right('0' + rtrim(month(getDate())-2),2))
      end
  group by phase_code, accounting_period
  </code></pre>