Praca z tabelami kalendarza

Analiza szeregów czasowych w SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Czym jest tabela kalendarza?

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	...

Zawartość tabeli kalendarza

Kolumny ogólne

Date
Nazwa dnia
Czy weekend

Rok kalendarzowy

Miesiąc kalendarzowy
Kwartał kalendarzowy
Rok kalendarzowy

Rok obrachunkowy

Tydzień roku obrachunkowego
Kwartał obrachunkowy
Pierwszy dzień roku obrachunkowego

Kolumny specjalne

Nazwa święta
Dane księżycowe
Tydzień ISO roku

Budowanie tabeli kalendarza

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],
    ...

Korzystanie z tabeli kalendarza

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

Korzystanie z tabeli kalendarza

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

Krótka uwaga o 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;

Krótka uwaga o 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;

Czas na ćwiczenia!

Analiza szeregów czasowych w SQL Server