Working with calendar tables

Time Series Analysis in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

What is a calendar table?

SELECT *
FROM dbo.Calendar;

DateKey	Date	Day	DayOfWeek	DayName	...
20000101	2000-01-01	1	7	Saturday	...
20000102	2000-01-02	2	1	Sunday	...
20000103	2000-01-03	3	2	Monday	...

Contents of a calendar table

General Columns

Date
Day Name
Is Weekend

Calendar Year

Calendar month
Calendar quarter
Calendar year

Fiscal Year

Fiscal week of year
Fiscal quarter
Fiscal first day of year

Specialized Columns

Holiday name
Lunar details
ISO week of year

Building a calendar table

CREATE TABLE dbo.Calendar
(
    DateKey INT NOT NULL,
    [Date] DATE NOT NULL,
    [Day] TINYINT NOT NULL,
    DayOfWeek TINYINT NOT NULL,
    DayName VARCHAR(10) NOT NULL,
    ...
)

SELECT
    CAST(D.DateKey AS INT) AS DateKey,
    D.[DATE] AS [Date],
    CAST(D.[day] AS TINYINT) AS [day],
    CAST(d.[dayofweek] AS TINYINT) AS [DayOfWeek],
    CAST(DATENAME(WEEKDAY, d.[Date]) AS VARCHAR(10)) AS [DayName],
    ...

Using a calendar table

SELECT
    c.Date
FROM dbo.Calendar c
WHERE
    c.MonthName = 'April'
    AND c.DayName = 'Saturday'
    AND c.CalendarYear = 2020
ORDER BY
    c.Date;

Date
2020-04-04
2020-04-11
2020-04-18
2020-04-25

Using a calendar table

SELECT
    c.Date
FROM dbo.Calendar c
WHERE
    c.MonthName = 'April'
    AND c.DayName = 'Saturday'
    AND c.CalendarYear = 2020
ORDER BY
    c.Date;

Date
2020-04-04
2020-04-11
2020-04-18
2020-04-25

A quick note on APPLY()

SELECT
    FYStart =
        DATEADD(MONTH, -6,
            DATEADD(YEAR,
                DATEDIFF(YEAR, 0,
                    DATEADD(MONTH, 6, d.[date])), 0)),
    FiscalDayOfYear =
        DATEDIFF(DAY,
            DATEADD(MONTH, -6,
                DATEADD(YEAR,
                    DATEDIFF(YEAR, 0,
                        DATEADD(MONTH, 6, d.[date])), 0)), d.[Date]) + 1,
    FiscalWeekOfYear =
        DATEDIFF(WEEK,
            DATEADD(MONTH, -6,
                DATEADD(YEAR,
                    DATEDIFF(YEAR, 0,
                        DATEADD(MONTH, 6, d.[date])), 0)), d.[Date]) + 1
FROM dbo.Calendar d;

A quick note on APPLY()

SELECT
    fy.FYStart,
    FiscalDayOfYear = DATEDIFF(DAY, fy.FYStart, d.[Date]) + 1,
    FiscalWeekOfYear = DATEDIFF(WEEK, fy.FYStart, d.[Date]) + 1
FROM dbo.Calendar d
    CROSS APPLY
    (
        SELECT FYStart =
            DATEADD(MONTH, -6,
                DATEADD(YEAR,
                    DATEDIFF(YEAR, 0,
                        DATEADD(MONTH, 6, d.[date])), 0))
    ) fy;

Let's practice!

Time Series Analysis in SQL Server