Archives

Using a Date Table to Track Custom Fiscal Years/Periods

Calendar Tables are only useful if they reflect how your company evaluates its data. Therefore, any company using non-standard financial fiscal years and periods will require customization with their Calendar or Date Table. In this post, I’ll provide an example of how to do so using the M2M ERP System Database. For those who don’t use M2M, the notes should serve as an example of how to do so with other systems.

The most effective way to determine the fiscal setup of a company is to determine how its major systems track the data. In M2M the source is the GL Rules and Periods (GLRule) table. There are four primary fields of interest, which I’ve aliased, and they are listed in the following query and screenshot.

SELECT GLR.fcname AcctYearName
      ,GLR.fnnumber PeriodNo
      ,GLR.fdstart FirstDayOfPeriod
      ,GLR.fdend LastDayOfPeriod
  FROM dbo.glrule GLR

As you can see, there is one record for each fiscal period in GLRule. FCName is a Char(20) field and typically contains values like “FY 2005″ or “Fiscal Year 2005″. FNNumber contains the period number and is an Int datatype. FDStart and FDEnd are Datetime fields and represent the first and last days of each period.

What follows are the update statements which I use to customize the fiscal periods of my calendar table. For your convenience, you can access the entire script here****. Copy and paste the entire query, replace the database and table name, and run it. I’ve dissected each step of the script below so those not using M2M can understand my logic and mimic those steps with their own systems.

Six fields are updated by the first statement. Notice that I’ve joined the Calendar Table to GLRule with a between statement. I’m using the pattern matching of Patindex to find the four digit number for Fiscal Year. However, if the user entered something like “Two Thousand Five” as a Fiscal Year, this method will not work. Start and end of period fields are taken directly out of GLRule as well. FiscalDayOfPeriod is calculated in this step as well for good measure.

Use M2MDataXX
GO
UPDATE TST
   SET TST.[FiscalYearName] =
        substring (GLR.fcName, patindex ('%[1,2][9,0,1,2][0-9][0-9]%', GLR.fcName), 4)
      ,TST.[FiscalYearPeriod] =
        (substring (GLR.fcName, patindex ('%[1,2][9,0,1,2][0-9][0-9]%', GLR.fcName), 4))
        + + (GLR.fnnumber / 100.00)
      ,TST.[FiscalPeriod] = GLR.fnnumber
      ,TST.[FiscalStartOfPeriodDate] = GLR.fdstart
      ,TST.[FiscalEndOfPeriodDate] = GLR.fdend
      ,TST.[FiscalDayOfPeriod] = (DATEDIFF (dd, GLR.fdstart, TST.[DATE]) + 1)
  FROM  DateTestStnd.dbo.DimDate TST
       INNER JOIN
        glrule GLR
       ON TST.DATE BETWEEN GLR.fdstart AND GLR.fdend

In this step, I’ve calculated the beginning and end of Fiscal Years. I group the record set in GLRule by Fiscal Year and then use min/max to determine the first and last day in each group.

UPDATE TST
   SET TST.[FiscalStartOfYearDate] = M2MYEARS.[FiscalStartOfYearDate]
      ,TST.[FiscalEndOfYearDate] = M2MYEARS.[FiscalEndOfYearDate]
  FROM  DateDatabase.dbo.DimDate TST
       INNER JOIN
        (SELECT substring (GLR.fcName, patindex ('%[1,2][9,0,1,2][0-9][0-9]%', GLR.fcName), 4)
                 FiscalYearName
               ,min (GLR.fdstart) [FiscalStartOfYearDate]
               ,max (GLR.fdend) [FiscalEndOfYearDate]
           FROM glrule GLR
         GROUP BY GLR.fcname) M2MYEARS
       ON TST.FiscalYearName = M2MYEARS.FiscalYearName

In the next step, I calculate the FiscalDayofYear and FiscalYearDayOfYear field. While I could have included this code in the previous step, it’s much easier to read and understand as a separate step. The FiscalDayOfYear calculation is simple and is simply the number of days between the current record and the first day Fiscal Year calculated previously. The FiscalYearDayOfYear is the same calculation, but I convert the integer (day) to a decimal and add it to the Fiscal Year integer. This makes drill down and grouping easier.

UPDATE TST
   SET TST.[FiscalDayOfYear] = (DATEDIFF (dd, [FiscalStartOfYearDate], TST.[DATE]) + 1)
      ,TST.[FiscalYearDayOfYear] =
        (datepart (year, TST.[DATE])
         + cast (
            (datediff (dd, dateadd (yy, datediff (yy, 0, TST.[DATE]), 0), TST.[DATE]) + 1)
            / 1000.00 AS NUMERIC (3, 3)))
   FROM  DateDatabase.dbo.DimDate TST

The next section looks complex because I have to determine whether the number of Fiscal Periods can be broken into Quarters and Semesters. If a company used 10 Fiscal Periods per year, the concept of Quarters doesn’t make sense, but Semesters do. To make the code easier to understand, I’ve numbered the sections.

Section 1 checks whether the number of periods per fiscal year can be divided into quarters and semesters and the number of periods in each. Section 2 includes the period start and end dates so that the values of quarter and semester can be related back to the date table. Values for quarter or semester will be set to 0 if they aren’t valid in a particular M2M install.

UPDATE TST
   SET [FiscalQuarter] =
        CASE M2MYEARS.Quarterific
         WHEN 'Yes' THEN (ceiling (CAST (M2MYEARS.PeriodNo AS DECIMAL (4, 2)) / M2MYEARS.QuarterSize))
         ELSE 0
        END
      ,[FiscalSemester] =
        CASE M2MYEARS.Semesterific
         WHEN 'Yes' THEN (ceiling (CAST (M2MYEARS.PeriodNo AS DECIMAL (4, 2)) / M2MYEARS.SemesterSize))
         ELSE 0
        END 
  FROM  DateTestStnd.dbo.DimDate TST
       INNER JOIN
 -------------- 2. Include Start and End of Period Values. 
        (SELECT fcname FYName
               ,fdstart PeriodStart
               ,fdend PeriodEnd
               ,fnnumber PeriodNo
               ,GLRAGG.QuarterSize
               ,GLRAGG.Quarterific
               ,GLRAGG.SemesterSize
               ,GLRAGG.Semesterific
           FROM  dbo.glrule GLR
                INNER JOIN
  -------------- 1. Calculate whether Quarters and Semesters are Valid (and their values)
                 (SELECT fcname AGGFYName
                        ,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 dbo.glrule
                  GROUP BY fcname) GLRAGG
                ON GLR.FCNAME = GLRAGG.AGGFYNAME) M2MYEARS
 -------------- End of 1
       ON TST.DATE >= M2MYEARS.PeriodStart AND TST.DATE <= M2MYEARS.PeriodEnd
  -------------- End of 2

Workday calculation is also somewhat difficult to understand. In M2M this information is kept in the Production Calendar (SCCALN) table. The fields we care about are:

  • fcYrMon – Year and month in the format of YYYY/MM (2011/02). Unfortunately these are not per Fiscal Year or Period, they are by Calendar Year and Month.
  • fcShifts – A Varchar (31) field with the number of Shifts per day.


Needless to say, we need an easier way than parsing through this table every time we need to calculate work days. The join is a little strange because the Date Table CalendarYear and CalendarMonth fields are SmallInt and need to be cast as Varchar. I’ve used a left join because many M2M companies don’t use the Production Calendar so SCCALN may lack records for fiscal periods.

Basically the code checks for a corresponding record in SCCALN. If one does not exist, then the typical Monday through Friday work week is used by default. If it does exist the fcShifts field is parsed, and if there are one or more shifts for a particular day, then the date is designated a work day.

UPDATE TST
   SET WorkDay =
        CASE
         WHEN wc.fcShifts IS NULL THEN
           CASE WHEN TST.CalendarDayOfWeek IN (1, 7) THEN 'No Work' ELSE 'Work Day' END
         ELSE
           CASE
            WHEN substring (WC.fcShifts, TST.CalendarDayOfMonth, 1) = '0' THEN 'No Work'
            ELSE 'Work Day'
           END
        END
      ,IsWorkDay =
        CASE
         WHEN WC.fcShifts IS NULL THEN 
           CASE WHEN TST.CalendarDayOfWeek IN (1, 7) THEN 0 ELSE 1 END
         ELSE 
           CASE WHEN substring (WC.fcShifts, TST.CalendarDayOfMonth, 1) = '0' THEN 0 ELSE 1 
           END
        END
  FROM  DateDatabase.dbo.DimDate TST
       LEFT JOIN
        dbo.ScCaln WC
       ON WC.fcYrMon = cast (TST.CalendarYear AS Varchar (4))
           + '/'
           + right ('0' + cast (TST.CalendarMonth AS Varchar (2)), 2)

The final bit of code updates the FiscalWeekName and FiscalYearWeekName. This code is tailored to my current employer, and I consider it optional because it may not pertain to you. The logic is as follows:

  1. The first fiscal week starts on the first day of the fiscal period.
  2. Fiscal weeks end on Friday and begin on Saturday.
  3. A “partial week” where the week starts on a day other than Saturday is added to the next full week. Partial weeks ending a period are considered their own week.
  4. Week names follow the pattern of [FiscalPeriod].[WeekNumber] both of which are integers. For example, the second week of the fifth period would be designated as “5.2″.

Again, this is a specific requirement and it may not apply to most companies.

UPDATE TST
   SET FiscalWeekName =
        TST.FiscalPeriod
        + CASE ( (SELECT count (*)
                    FROM DateDatabase.dbo.DimDate TMP
                   WHERE TMP.CalendarDayOfWeek = 7
                         AND TMP.Date BETWEEN TST.FiscalStartOfPeriodDate AND TST.Date)
                / 10.0)
           WHEN 0 THEN
            .1
           ELSE
            ( (SELECT count (*)
                 FROM DateDatabase.dbo.DimDate TMP
                WHERE TMP.CalendarDayOfWeek = 7
                      AND TMP.Date BETWEEN TST.FiscalStartOfPeriodDate AND TST.Date)
             / 10.0)
          END
  FROM DateDatabase.dbo.DimDate TST
-- Updates FiscalYearWeekName from FiscalWeekName
UPDATE TST
   SET TST.FiscalYearWeekName =
          cast (TST.FiscalYearName AS CHAR (4))
        + '.'
        + (CASE len (TST.FiscalWeekName) WHEN 3 THEN '0' ELSE '' END)
        + cast (TST.FiscalWeekName AS CHAR (4))
  FROM DateDatabase.dbo.DimDate TST

In most systems, new fiscal years and periods are added once a year. The update script will need to be run after that happens. With Made2Manage, and presumably other systems, triggers could be used to detect those changes and then run the update script. In my own situation, I’ve chosen to save this update script as a scheduled job which runs weekly. This may seem wasteful, but the update script completes very quickly.

Technically, I could have integrated this script with the one I posted previously which would have been faster and more efficient. However, I use two scripts for several reasons. First, this method is more flexible as I can create any Fiscal Update script I want without editing the main calendar script. Second, the Fiscal Update script will need to be run periodically to get updates and dropping/recreating the entire date table would be wasteful. Finally, most systems do not track Fiscal Years and Periods a decade or more into the future. Therefore, the first script is required to set default values for the Fiscal fields and only those that exist in the source system are updated. The user will not receive an error on a report because a future fiscal date value was not supplied.

Well, there you have it. The last two articles have described how I create date tables and customize the Fiscal fields from a source system, in this case Made2Manage.

Any questions or suggestions to make the process better?

Related posts:

1 comment to Using a Date Table to Track Custom Fiscal Years/Periods

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>