0

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.

EDIT: I think I might actually have stumbled on the answer.

With the LastFridayInOctober CTE and the DateDimension table, create a new CTE that creates the results I want.

Then LEFT OUTER JOIN the new CTE with the main query, on EndOfFiscalYear

;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
),

Test AS(
    SELECT TheDate,
            LastDayOfFiscalYear,
            EndOfFiscalYear,
            FirstDayOfFiscalYear
    FROM (SELECT DD.TheDate,
                    DATEADD(day, 1, DD.TheDate) 'LastDayOfFiscalYear',
                    YEAR(DD.TheDate) 'EndOfFiscalYear',
                    YEAR(DD.TheDate) + 1 'NextFiscalYear',
                    DATEADD(day, 2, DD.TheDate) 'FirstDayOfFiscalYear'
            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
        ) A
)

SELECT YEAR(DD.TheDate) + 1 'FiscalYear',
        DATEADD(day, 2, DD.TheDate) 'FirstDayOfFiscalYear',
        T.LastDayOfFiscalYear
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
    LEFT OUTER JOIN Test T ON YEAR(DD.TheDate) + 1 = T.EndOfFiscalYear
ORDER BY DD.TheDate

FiscalYear..........FirstDayOfFiscalYear..........LastDayOfFiscalYear
1922                1921-10-30                    1922-10-28
1923                1922-10-29                    1923-10-27
1924                1923-10-28                    1924-11-01
1925                1924-11-02                    1925-10-31
1926                1925-11-01                    1926-10-30

I'd love for someone to peer review this answer, please. Here is the execution plan

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.