Trying to set a variable inside a case statement.

Question

I'm trying to update a date dimension table from the accounting years table of our ERP System. If I run the following Query:

SELECT fcname FYName
      ,min(fdstart) YearStart
      ,max(fdend) YearEnd
      ,max(fnnumber) PeriodCount
FROM M2MData01.dbo.glrule GLR
GROUP BY fcname

I get the following data:

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

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:
1. If PeriodCount is divisible by 4 then the number of periods in a quarter is PeriodCount/4.
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.

The problem is that I cannot be guaranteed that everyone uses 12 periods, some companies I support use a different number such as 10.

I started creating the following select statement:

    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 >= GLR.YearStart AND TST.DATE <= GLR.YearEnd

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?

Answer 1

Not sure what you want to do here - you can assign variable outside case statement in select clause. Such as

SELECT
   SomeCol,
   @var = CASE
     WHEN condition1 THEN some value
     WHEN condition2 THEN other value
   END,
   OtherCol
FROM
   ...

Note that @var value be set to the value evaluated at the last row. As said earlier, I am not sure how you intend to use you @quartersize variable. If the value is needed on every row then u shouldn't be using variable at all.