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.

Answer 2

It may not be the most elegant solution, but here is what I ended up with.

I linked a copy of the script details to a grouped by version of the same thing.

SELECT fcname FYName, fdstart PeriodStart, fdend PeriodEnd, fnnumber PeriodNo, GLRAGG.AGGFYName,
       GLRAGG.QuarterSize, GLRAGG.PeriodCount, GLRAGG.Quarterific, GLRAGG.SemesterSize, GLRAGG.Semesterific
FROM   M2MData01.dbo.glrule GLR
     INNER JOIN
       (SELECT fcname AGGFYName, min(fdstart) YearStart,
               MAX(fdend) YearEnd, MAX(fnnumber) PeriodCount,
               (Max(fnnumber) / 4) QuarterSize, CASE WHEN Max(fnnumber) % 4 = 0 THEN 'Yes' ELSE 'No' END AS Quarterific,
               (Max(fnnumber) / 2) SemesterSize, CASE WHEN Max(fnnumber) % 2 = 0 THEN 'Yes' ELSE 'No' END AS Semesterific
        FROM M2MData01.dbo.glrule
        GROUP BY fcname) GLRAGG
     ON GLR.FCNAME = GLRAGG.AGGFYNAME

This isn't a big deal because that table only has 12 rows for each year, in this case only 132 total rows.

That produces every fiscal period with the total number of periods in each Fiscal Year and whether it can be evenly divisible by 4 and 2. It then uses the "Quarterific" value to determine whether to do so in the update statement and I can get by wtihout using variables.

It may not be the best way, but it works and is performant given the small data set.