Введение:

При работе с SQL Server часто бывает полезно иметь таблицу дат, содержащую информацию о днях, неделях, месяцах, кварталах, годах и днях недели. Эта таблица дат может использоваться в различных сценариях, таких как вычисление фискальных и календарных интервалов, преобразование между форматами даты или выборка частей даты. В этой статье мы рассмотрим, как создать таблицу дат в SQL Server и заполнить ее информацией о праздниках.

Таблицы дат:

Таблица дат, также известная как DimDate, – это таблица, содержащая список дат вместе с их соответствующими атрибутами. Эти атрибуты могут быть вычисляемыми столбцами, которые предоставляют информацию о дате, такую как день, неделя, месяц, квартал, год и день недели. Используя таблицу дат, вы можете избежать необходимости писать сложный код для вычисления этих атрибутов каждый раз, когда они вам нужны.

Создание таблицы дат:

Для создания таблицы дат вы можете создать таблицу с необходимыми столбцами для атрибутов даты. Например:

CREATE TABLE [dbo].[DimDate] (
    [DimDateID] [int] NOT NULL PRIMARY KEY,
    [DateValue] [date] NOT NULL UNIQUE,
    [Day] AS DAY(DateValue),
    [Week] AS DATEPART(WEEK, DateValue),
    [Month] AS MONTH(DateValue),
    [Quarter] AS DATEPART(QUARTER, DateValue),
    [Year] AS YEAR(DateValue),
    [DayOfWeek] AS DATEPART(WEEKDAY, DateValue),
    [IsCanadianHoliday] [bit] NOT NULL DEFAULT((0)),
    [IsUSHoliday] [bit] NOT NULL DEFAULT((0))
)

После создания таблицы вы можете заполнить ее датами, используя подход с встроенной таблицей-последовательностью. Этот подход генерирует последовательность чисел и использует ее для вычисления дат в заданном диапазоне. Например:

DECLARE @StartDate date = '2000-01-01',
        @EndDate date = '2050-12-31';

WITH N AS (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
        n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM
        (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(n)
)
INSERT INTO [dbo].[DimDate] (DimDateID, DateValue)
SELECT
    CAST(CONVERT(CHAR(8), DATEADD(DAY, n, @StartDate), 112) AS INT),
    DATEADD(DAY, n, @StartDate)
FROM
    N;

Обновление столбцов праздников:

После заполнения таблицы дат вы можете обновить столбцы праздников на основе определенных правил праздников. Например, для обновления столбца праздника “День благодарения” в США, вы можете использовать следующий оператор обновления:

UPDATE [dbo].[DimDate]
SET IsUSHoliday = 1
WHERE [Month] = 11
    AND [DayOfWeek] = 5
    AND [Day] BETWEEN 22 AND 28;

Аналогично, вы можете обновить столбец праздника “День благодарения” в Канаде с помощью следующего оператора обновления:

UPDATE [dbo].[DimDate]
SET IsCanadianHoliday = 1
WHERE [Month] = 10
    AND [DayOfWeek] = 2
    AND [Day] BETWEEN 8 AND 14;

Использование вычисляемых столбцов:

Если вы предпочитаете, вы также можете использовать вычисляемые столбцы для вычисления значений праздников вместо их обновления. Это может быть полезно, когда в вычислении дат праздников вовлечены сложные вычисления. Например, для вычисления Воскресенья Пасхи вы можете использовать следующий вычисляемый столбец:

[EasterSunday] AS (
    SELECT DATEFROMPARTS([Year], [Month], [Day])
    FROM [dbo].[DimDate]
    CROSS APPLY (
        SELECT [Year] AS y
    ) _y
    CROSS APPLY (
        SELECT y / 100 AS c, y - 19 * (y / 19) AS n
    ) _nc
    CROSS APPLY (
        SELECT (c - 17) / 25 AS k
    ) _k
    CROSS APPLY (
        SELECT c - c / 4 - (c - k) / 3 + 19 * n + 15 AS i1
    ) _i1
    CROSS APPLY (
        SELECT i1 - 30 * (i1 / 30) AS i2
    ) _i2
    CROSS APPLY (
        SELECT i2 - (i2 / 28) * (1 - (i2 / 28) * (29 / (i2 + 1))) * ((21 - n) / 11) AS i
    ) _i
    CROSS APPLY (
        SELECT y + y / 4 + i + 2 - c + c / 4 AS j1
    ) _j1
    CROSS APPLY (
        SELECT j1 - 7 * (j1 / 7) AS j
    ) _j
    CROSS APPLY (
        SELECT i - j AS el
    ) _el
    CROSS APPLY (
        SELECT 3 + (el + 40) / 44 AS m
    ) _m
    CROSS APPLY (
        SELECT el + 28 - 31 * (m / 4) AS d
    ) _d
)

Заключение:

Создание таблицы дат в SQL Server может значительно упростить вычисления и запросы, связанные с датами. Используя вычисляемые столбцы и операторы обновления, вы можете легко пометить определенные даты как праздники. Кроме того, использование подхода с встроенной таблицей-последовательностью позволяет эффективно заполнять таблицу дат диапазоном дат. Включение этих техник в ваши проекты SQL Server может улучшить производительность и упростить ваш код.