title: Trying to set a variable inside a case statement.
body: <p>I'm trying to update a date dimension table from the accounting years table of our ERP System. If I run the following Query:</p>
  
  <pre><code>SELECT fcname FYName
        ,min(fdstart) YearStart
        ,max(fdend) YearEnd
        ,max(fnnumber) PeriodCount
  FROM M2MData01.dbo.glrule GLR
  GROUP BY fcname
  </code></pre>
  
  <p>I get the following data: </p>
  
  <pre><code>FYName            YearStart                   YearEnd                 PeriodCount
  FY 2000                 1/1/2000 12:00:00 AM    12/31/2000 12:00:00 AM  12
  FY 2001                 1/1/2001 12:00:00 AM    12/31/2001 12:00:00 AM  12
  FY 2002                 1/1/2002 12:00:00 AM    12/31/2002 12:00:00 AM  12
  FY 2003                 1/1/2003 12:00:00 AM    12/31/2003 12:00:00 AM  12
  FY 2004                 1/1/2004 12:00:00 AM    12/31/2004 12:00:00 AM  12
  FY 2005                 1/1/2005 12:00:00 AM    12/31/2005 12:00:00 AM  12
  FY 2006                 1/1/2006 12:00:00 AM    12/31/2006 12:00:00 AM  12
  FY 2007                 1/1/2007 12:00:00 AM    12/31/2007 12:00:00 AM  12
  FY 2008                 1/1/2008 12:00:00 AM    12/31/2008 12:00:00 AM  12
  FY 2009                 1/1/2009 12:00:00 AM    12/31/2009 12:00:00 AM  12
  FY 2010                 1/1/2010 12:00:00 AM    12/31/2010 12:00:00 AM  12
  </code></pre>
  
  <p>In my case my company has 12 periods per year which roughly correspond to months. Basically, I am trying to create an update statement to set Fiscal Quarters which will follow this logic:<br>
  1. If PeriodCount is divisible by 4 then the number of periods in a quarter is PeriodCount/4.<br>
  2. If PeriodNumber is in the first quarter (in this case periods 1 through 3) then FiscalQuarter =1 and so on for quarters 2 through 4.   </p>
  
  <p>The problem is that I cannot be guaranteed that everyone uses 12 periods, some companies I support use a different number such as 10. </p>
  
  <p>I started creating the following select statement:</p>
  
  <pre><code>    DECLARE @QuarterSize   INT
      DECLARE @SemesterSize   INT
  
      SELECT TST.Date, 
  CASE WHEN glr.PeriodCount % 4 = 0 THEN    
  -- Can Be divided into quarters. Quarter size is PeriodCount/4
  set  @quartersize = (GLR.PeriodCount/4)
  
      CASE     
  
  
      END
  
          ELSE 0
      End
      FROM    m2mdata01.dbo.AllDates TST
           INNER JOIN (
            SELECT fcname FYName
            ,min(fdstart) YearStart
            ,MAX(fdend) YearEnd
            ,MAX(fnnumber) PeriodCount
      FROM M2MData01.dbo.glrule GLR
      GROUP BY fcname ) GLR
  
           ON TST.DATE &gt;= GLR.YearStart AND TST.DATE &lt;= GLR.YearEnd
  </code></pre>
  
  <p>Can I set the value of a variable inside a case statement like this? What's the best way to accomplish this? Am I forced to use a cursor statement and check each date in my dimension against the range in the table above? </p>