CODEWITHSUNDEEP
sql-server
Jerome Pillay
Jerome Pillay

Reputation: 23

SQL Server Build a Dynamic Calendar Table

I need to create a Fiscal Calendar table with the following criteria:

The duration of the months are based on the 26th to the 25th of the following month. For example, here is a gist of the structure:

| Fiscal Year | Month Number | Month Start Date | Month End Date |
|-------------|--------------|------------------|----------------|
| 2019        | 1            | 2019-03-26       | 2019-04-25     |
| 2019        | 2            | 2019-04-26       | 2019-05-25     |
| 2019        | 3            | 2019-05-26       | 2019-06-25     |
| 2019        | 4            | 2019-06-26       | 2019-07-25     |
| 2019        | 5            | 2019-07-26       | 2019-08-25     |
| 2019        | 6            | 2019-08-26       | 2019-09-25     |
| 2019        | 7            | 2019-09-26       | 2019-10-25     |
| 2019        | 8            | 2019-10-26       | 2019-11-25     |
| 2019        | 9            | 2019-11-26       | 2019-12-25     |
| 2019        | 10           | 2019-12-26       | 2020-01-25     |
| 2019        | 11           | 2020-01-26       | 2020-02-25     |
| 2019        | 12           | 2020-02-26       | 2020-03-25     |

This will need to be a rolling calendar for adding more years going forward.

Upvotes: 0

Views: 1461

Answers (1)

Ross Bush
Ross Bush

Reputation: 15155

You can use a query similar to below to populate a physical table to get better performance.

DECLARE @StartDate DATETIME = '01/01/2015' 
DECLARE @EndDate DATETIME = '12/01/2016' 

    ;WITH OrderedDays as 
    ( 
        SELECT CalendarDate = @StartDate
        UNION ALL 
        SELECT CalendarDate = DATEADD(DAY, 1, CalendarDate)
        FROM OrderedDays WHERE DATEADD (DAY, 1, CalendarDate) <= @EndDate 
    ),
    Calendar AS
    (
        SELECT
            DayIndex = ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY CalendarDate),
            CalendarDate,
            CalenderDayOfMonth = DATEPART(DAY, CalendarDate),
            CalenderMonthOfYear = DATEPART(MONTH, CalendarDate),
            CalendarYear = DATEPART(YEAR, CalendarDate),
            CalenderWeekOfYear = DATEPART(WEEK, CalendarDate),
            CalenderQuarterOfYear = DATEPART(QUARTER, CalendarDate),
            CalenderDayOfYear =  DATEPART(DAYOFYEAR, CalendarDate),
            CalenderDayOfWeek =  DATEPART(WEEKDAY, CalendarDate),
            CalenderWeekday =  DATENAME(WEEKDAY, CalendarDate),
            StartOfMonth =  DATEADD(MONTH, DATEDIFF(MONTH, 0, CalendarDate), 0),
            EndOfMonth = DATEADD (DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CalendarDate) + 1, 0))
        FROM
            OrderedDays                 
    )
    SELECT 
        FiscalYear = CalendarYear,
        MonthNumber = CalenderMonthOfYear,
        MonthStartDate = MIN(StartOfMonth),
        MonthEndDate = MIN(EndOfMonth) 
    FROM 
        Calendar
    GROUP BY 
        CalendarYear,
        CalenderMonthOfYear
    ORDER BY
        CalendarYear,
        CalenderMonthOfYear

    OPTION (MAXRECURSION 0)

Based on feedback from Larnu, here is a version using a tally instead.

DECLARE @StartDate DATETIME = '01/01/2019'
DECLARE @NumberOfDays INT = 10000

;WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    R2(N) AS (SELECT 1 FROM R1 a, R1 b),
    R3(N) AS (SELECT 1 FROM R2 a, R2 b), 
    Tally(Number) AS (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R3)

,WithTally AS   
(
    SELECT CalendarDate = DATEADD(DAY,T.Number,@StartDate)
    FROM
        Tally T
    WHERE
        T.Number < @NumberOfDays
)   


,Calendar AS
(
    SELECT
        DayIndex = ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY CalendarDate),
        CalendarDate,
        CalenderDayOfMonth = DATEPART(DAY, CalendarDate),
        CalenderMonthOfYear = DATEPART(MONTH, CalendarDate),
        CalendarYear = DATEPART(YEAR, CalendarDate),
        CalenderWeekOfYear = DATEPART(WEEK, CalendarDate),
        CalenderQuarterOfYear = DATEPART(QUARTER, CalendarDate),
        CalenderDayOfYear =  DATEPART(DAYOFYEAR, CalendarDate),
        CalenderDayOfWeek =  DATEPART(WEEKDAY, CalendarDate),
        CalenderWeekday =  DATENAME(WEEKDAY, CalendarDate),
        StartOfMonth =  DATEADD(MONTH, DATEDIFF(MONTH, 0, CalendarDate), 0),
        EndOfMonth = DATEADD (DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CalendarDate) + 1, 0))
    FROM
        WithTally                   
)
SELECT 
    FiscalYear = CalendarYear,
    MonthNumber = CalenderMonthOfYear,
    MonthStartDate = MIN(StartOfMonth),
    MonthEndDate = MIN(EndOfMonth) 
FROM 
    Calendar
GROUP BY 
    CalendarYear,
    CalenderMonthOfYear
ORDER BY
    CalendarYear,
    CalenderMonthOfYear

OPTION (MAXRECURSION 0)

Upvotes: 1

Related Questions