1

I have a table that contains the following fiscal year definitions:

FiscalYear     | Period     | PeriodStartDate     | PeriodEndDate
=======================================================================
2020           |     1      | 2019-10-27 00:00:00 | 2019-11-23 00:00:00
2020           |     2      | 2019-11-24 00:00:00 | 2019-12-21 00:00:00
2020           |     3      | 2019-12-22 00:00:00 | 2020-01-18 00:00:00
2020           |     4      | 2020-01-19 00:00:00 | 2020-02-15 00:00:00
2020           |     5      | 2020-02-16 00:00:00 | 2020-03-14 00:00:00
2020           |     6      | 2020-03-15 00:00:00 | 2020-04-11 00:00:00
2020           |     7      | 2020-04-12 00:00:00 | 2020-05-09 00:00:00
2020           |     8      | 2020-05-10 00:00:00 | 2020-06-06 00:00:00
2020           |     9      | 2020-06-07 00:00:00 | 2020-07-04 00:00:00
2020           |     10     | 2020-07-05 00:00:00 | 2020-08-01 00:00:00
2020           |     11     | 2020-08-02 00:00:00 | 2020-08-29 00:00:00
2020           |     12     | 2020-08-30 00:00:00 | 2020-09-26 00:00:00
2020           |     13     | 2020-09-27 00:00:00 | 2020-10-24 00:00:00

After reading Aaron Bertrand's article on how to to build a calendar table (https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/), I would like to incorporate his columns as well as break down my fiscal table as follows:

  • FiscalYear -- in FiscalReportingPeriod_tb (FiscalYear)
  • FiscalQuarter
  • FiscalPeriod -- in FiscalReportingPeriod_tb (Period)
  • FiscalWeek
  • FiscalDay
  • QuarterWeek
  • QuarterDay
  • PeriodWeek
  • PeriodDay
  • FirstOfFiscalMonth -- in FiscalReportingPeriod_tb (PeriodStartDate)
  • EndOfFiscalMonth -- in FiscalReportingPeriod_tb (PeriodEndDate)
  • StartOfFiscalYear
  • EndOfFiscalYear
  • FirstOfFiscalQuarter
  • EndOfFiscalQuarter

Short of creating customer functions to get the desired output, I am not sure how to incorporate my breakdown with how Aaron has created his tables.

EDIT: to help address Aaron's question, if I take the FiscalQuarter items out of it (never made sense to me in the first place), this is what I am looking for:

FiscalYear     | FiscalPeriod     | FiscalWeek     | FiscalDay     | PeriodWeek     | PeriodDay     | FirstOfFiscalMonth     | EndOfFiscalMonth     | StartOfFiscalYear     | EndOfFiscalYear     | TheDate
================================================================================================================================================================================================================
2020           |       1          |       1        |      1        |      1         |      1        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-27
2020           |       1          |       1        |      2        |      1         |      2        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-28
2020           |       1          |       1        |      3        |      1         |      3        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-29
2020           |       1          |       1        |      4        |      1         |      4        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-30
2020           |       1          |       1        |      5        |      1         |      5        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-31
2020           |       1          |       1        |      6        |      1         |      6        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-01
2020           |       1          |       1        |      7        |      1         |      7        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-02
2020           |       1          |       2        |      8        |      2         |      1        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-03
2020           |       1          |       2        |      9        |      2         |      2        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-04
2020           |       1          |       2        |      10       |      2         |      3        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-05
2020           |       1          |       2        |      11       |      2         |      4        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-06
2020           |       1          |       2        |      12       |      2         |      5        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-07
2020           |       1          |       2        |      13       |      2         |      6        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-08
2020           |       1          |       2        |      14       |      2         |      7        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-09
  • Can you help by showing some examples of what you want those columns to be? For example, do 2019-10-27/28/29/30/31 -> 2019-11-01/02 all belong to the same fiscal week? Is it because of the day of the week 10/27 falls on, or because that's the start of the period? Same for fiscal month. And for quarters, I don't even know where to start. How do you divide 13 periods into quarters? – Aaron Bertrand Jun 3 at 20:46
  • I've added a 2-week "desired end state" example to my question. Keep in mind that the sample data I provided is only for the current fiscal year; I have this information going back to 2009 – Kulstad Jun 3 at 21:36
  • 13 periods is atypical, in my experience, with companies choosing instead to adopt a 5 week period once each quarter, and sometimes for the last/first period to keep the beginning of the FY around the same time each year. – Graham Jun 4 at 15:22
2

This builds the table you are looking for:

WITH
ImportantDates AS (
    SELECT * FROM (
        VALUES
            /*
                This approach assumes Important Dates are continous
                A better approach would be to build from start dates
                to ensure that it s.
            */
            (2020,1 ,'2019-10-27','2019-11-23')
            ,(2020,2 ,'2019-11-24','2019-12-21')
            ,(2020,3 ,'2019-12-22','2020-01-18')
            ,(2020,4 ,'2020-01-19','2020-02-15')
            ,(2020,5 ,'2020-02-16','2020-03-14')
            ,(2020,6 ,'2020-03-15','2020-04-11')
            ,(2020,7 ,'2020-04-12','2020-05-09')
            ,(2020,8 ,'2020-05-10','2020-06-06')
            ,(2020,9 ,'2020-06-07','2020-07-04')
            ,(2020,10,'2020-07-05','2020-08-01')
            ,(2020,11,'2020-08-02','2020-08-29')
            ,(2020,12,'2020-08-30','2020-09-26')
            ,(2020,13,'2020-09-27','2020-10-24')
    ) V(FiscalYear,Period,PeriodStartDate,PeriodEndDate)
),
Days as (
    SELECT 
        TOP (
            SELECT DATEDIFF(D, MIN(PeriodStartDate), MAX(PeriodEndDate)) 
            FROM ImportantDates
        ) -- How high we want to count
        DATEADD(
            D, 
            ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, -- ROW_NUMBER is 1 Based, so subtract 1
            (SELECT MIN(PeriodStartDate) FROM ImportantDates)
        ) AS TheDate
    FROM
        sys.columns a CROSS JOIN sys.columns b -- something with a lot of rows

),
Segement AS (
    SELECT
        FiscalYear
        ,Period FiscalPeriod
        ,DATEDIFF(WEEK, MIN(TheDate) OVER (PARTITION BY FiscalYear), TheDate) + 1 FiscalWeek 
        ,DATEDIFF(DAY, MIN(TheDate) OVER (PARTITION BY FiscalYear), TheDate ) + 1 FiscalDay
        ,DATEDIFF(WEEK, MIN(TheDate) OVER (PARTITION BY FiscalYear, Period), TheDate) + 1 PeriodWeek
        ,DATEDIFF(DAY, MIN(TheDate) OVER (PARTITION BY FiscalYear, Period), TheDate ) + 1 PeriodDay
        ,MIN(TheDate) OVER (PARTITION BY FiscalYear, Period) FirstOfFiscalMonth
        ,MAX(TheDate) OVER (PARTITION BY FiscalYear, Period) EndOfFiscalMonth
        ,MIN(TheDate) OVER (PARTITION BY FiscalYear) StartOfFiscalYear
        ,MAX(TheDate) OVER (PARTITION BY FiscalYear) EndOfFiscalYear
        ,TheDate
    FROM
        Days JOIN ImportantDates ON TheDate BETWEEN PeriodStartDate AND PeriodEndDate

)
SELECT * FROM Segement
| improve this answer | |
  • thank you for this. I think there is something wrong with the Days CTE, tho. As I mentioned in my comment to Aaron Bertrand, I have FiscalYear data going back to 2009. If I modify the VALUES clause with a straight SELECT...FROM dbo.FiscalReportingPeriod_tb, your solution only returns 1,147 rows (2008-11-02 to 2011-11-14). I have tried using the OPTION(MAXRECURSION 0) hint, but it doesn't change the result. – Kulstad Jun 4 at 18:09
  • Try changing "sys.columns" to "sys.columns a cross join sys.columns b". I edited my answer to reflect that change. This will give you a maximum of over 1,000,000 days. – Graham Jun 4 at 21:07
  • thank you very much for this! – Kulstad Jun 4 at 21:43
  • If you think this saved your company some money, please encourage them to donate of portion of the expected savings to a local food bank. – Graham Jun 4 at 22:47
  • 1
    ,MIN(TheDate) OVER (PARTITION BY FiscalYear, DATEPART(WEEK, TheDate)) FirstOfFiscalWeek – Graham Jun 8 at 18:54

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.