t sql – Yet another SQL Server calendar table question


After the wonderful help I received on my original question, we have discovered that our current method of calculating our fiscal calendar is wrong.

Fiscal calendar rules:

  • fiscal year starts 2 calendar days after the last Friday of October
  • fiscal year ends on the last Friday of October
  • no business is conducted on Saturday
  • fiscal calendar runs Sunday to Saturday
  • there are 13 fiscal periods in the fiscal year
  • there are 4 weeks in each fiscal period, with the exception of the 13th fiscal period, which allows for an extra week

Using Aaron Bertrand’s calendar table, I have been able to identify the start of the fiscal periods as:

;WITH
LastFridayInOctober AS(
    SELECT YEAR(TheDate) 'TheYear',
            TheMonth,
            TheDayOfWeek,
            MAX(TheDayOfWeekInMonth) 'LastFriday'
    FROM dbo.DateDimension
    WHERE TheMonth = 10
        AND TheDayOfWeek = 6
    GROUP BY YEAR(TheDate),
                TheMonth,
                TheDayOfWeek
)

-- fiscal year start dates
SELECT DD.TheDate,
        LFIO.TheYear,
        LFIO.TheMonth,
        LFIO.TheDayOfWeek,
        LFIO.LastFriday,
        YEAR(DD.TheDate) + 1 'NextFiscalYear',
        DATEADD(day, 2, DD.TheDate) 'NextFiscalYearStart',
        DATENAME(weekday, DATEADD(day, 2, DD.TheDate)) 'NextFiscalYearStartDayName',
        '' 'NextFiscalYearEndDate',
        '' NextFiscalYearStartDayName       
FROM dbo.DateDimension DD
    INNER JOIN LastFridayInOctober LFIO ON DD.TheYear = LFIO.TheYear
        AND DD.TheMonth = LFIO.TheMonth
        AND DD.TheDayOfWeek = LFIO.TheDayOfWeek
        AND DD.TheDayOfWeekInMonth = LFIO.LastFriday
ORDER BY DD.TheDate;


TheDate..........TheYear..........TheMonth..........TheDayOfWeek..........LastFriday..........NextFiscalYear..........NextFiscalYearStart..........NextFiscalYearStartDayName..........NextFiscalYearEndDate..........NextFiscalYearStartDayName
1921-10-28       1921             10                6                     4                   1922                    1921-10-30                   Sunday                              <should be 1922-10-28>         <Saturday>
1922-10-27       1922             10                6                     4                   1923                    1922-10-29                   Sunday                              <should be 1922-10-27>         <Saturday>
1923-10-26       1923             10                6                     4                   1924                    1923-10-28                   Sunday                              <should be 1922-11-02>         <Saturday>
1924-10-31       1924             10                6                     5                   1925                    1924-11-02                   Sunday                              <should be 1922-11-01>         <Saturday>
1925-10-30       1925             10                6                     5                   1926                    1925-11-01                   Sunday                              <should be 1922-10-31>         <Saturday>
1926-10-29       1926             10                6                     5                   1927                    1926-10-31                   Sunday                              <should be 1922-10-30>         <Saturday>

If I can get the last Friday of October for the next year in the same query, I am sure I can break down my calendar data into the proper fiscal weeks and periods, but I just cant’ seem to get it. I’m sure the answer is staring me right in the face.