Do you find it challenging to maintain a list of holidays and working days for businesses in your SQL Server database? Are you tired of manually updating the holiday dates every year? In this article, we will discuss a solution to this problem by creating a dynamic holiday calendar in SQL Server.

Defining Holidays

Most holidays follow certain rules. For example, New Year’s Day, Christmas, and Independence Day occur on specific days but “float” if they fall on a weekend. There are also holidays that occur on a specific day of the week and a specific week of the month. To handle these variations, we can create a HolidayDefinition table that contains the country, holiday name, month, week, day, floating status, federal status, and business status.

CREATE TABLE dbo.HolidayDefinition (
    CountryCd CHAR(2) NOT NULL,
    Holiday VARCHAR(50) NOT NULL,
    MonthNbr TINYINT NOT NULL,
    WeekNbr TINYINT NOT NULL,
    DayNbr TINYINT NOT NULL,
    IsFloating BIT NOT NULL CONSTRAINT DF_HolidayDefinition_IsFloating DEFAULT (0),
    IsFederal BIT NOT NULL CONSTRAINT DF_HolidayDefinition_IsFederal DEFAULT (1),
    IsBusiness BIT NOT NULL CONSTRAINT DF_HolidayDefinition_IsBusiness DEFAULT (0),
    CONSTRAINT PK_HolidayDefinition PRIMARY KEY NONCLUSTERED (CountryCd ASC, Holiday ASC)
);

We can enforce some rules using check constraints. For example, if a holiday occurs on a specific day, the week number must be 0. If the week number is greater than 0, the day number corresponds to the day of the week. Only holidays falling on a specific day can “float”.

Loading Holiday Definitions

Next, we need to load the holiday definitions into the HolidayDefinition table. We can use a merge statement to insert new holidays and update existing ones. Here’s an example of how to merge the new dates:

-- create a CTE containing the source data to be merged into the target table
WITH cte_HolidayDefinition AS (
    SELECT CountryCd = 'US', Holiday = 'New Years Day', MonthNbr = '1', WeekNbr = '0', DayNbr = '1', IsFloating = '1', IsFederal = '1', IsBusiness = '1'
    UNION ALL
    SELECT CountryCd = 'US', Holiday = 'Martin Luther King Day', MonthNbr = '1', WeekNbr = '3', DayNbr = '2', IsFloating = '0', IsFederal = '1', IsBusiness = '1'
    -- add more holiday definitions here
)
-- merge the source data into the target table
MERGE dbo.HolidayDefinition tgt
USING (
    SELECT CountryCd, Holiday, MonthNbr, WeekNbr, DayNbr, IsFloating, IsFederal, IsBusiness
    FROM cte_HolidayDefinition
    EXCEPT
    SELECT CountryCd, Holiday, MonthNbr, WeekNbr, DayNbr, IsFloating, IsFederal, IsBusiness
    FROM dbo.HolidayDefinition
) src
ON tgt.CountryCd = src.CountryCd
    AND tgt.MonthNbr = src.MonthNbr
    AND tgt.WeekNbr = src.WeekNbr
    AND tgt.DayNbr = src.DayNbr
WHEN MATCHED THEN
    UPDATE SET
        CountryCd = src.CountryCd,
        Holiday = src.Holiday,
        MonthNbr = src.MonthNbr,
        WeekNbr = src.WeekNbr,
        DayNbr = src.DayNbr,
        IsFloating = src.IsFloating,
        IsFederal = src.IsFederal,
        IsBusiness = src.IsBusiness
WHEN NOT MATCHED THEN
    INSERT (CountryCd, Holiday, MonthNbr, WeekNbr, DayNbr, IsFloating, IsFederal, IsBusiness)
    VALUES (src.CountryCd, src.Holiday, src.MonthNbr, src.WeekNbr, src.DayNbr, ISNULL(src.IsFloating, 0), ISNULL(src.IsFederal, 1), ISNULL(src.IsBusiness, 0));

Building a Holiday Calendar

Now that we have the holiday definitions in the HolidayDefinition table, we can start using them to generate a holiday calendar. We can create a simple inline table-valued function that takes the country code and year as parameters and returns a table of holiday dates and other information.

CREATE FUNCTION dbo.fn_HolidayCalendar (
    @CountryCd CHAR(2) = 'US',
    @Year SMALLINT
)
RETURNS TABLE
AS
RETURN
SELECT
    h.CountryCd,
    h.Holiday,
    HolidayOn = CONVERT(DATE, CASE h.WeekNbr
        WHEN 0 THEN CASE h.IsFloating
            WHEN 0 THEN r.HolidayOn
            ELSE DATEADD(DAY, CASE DATENAME(WEEKDAY, r.HolidayOn)
                WHEN 'Sunday' THEN 1
                WHEN 'Saturday' THEN -1
                ELSE 0
            END, r.HolidayOn)
        END
        ELSE r.HolidayOn
    END),
    h.MonthNbr,
    h.WeekNbr,
    h.DayNbr,
    h.IsFloating,
    h.IsFederal,
    h.IsBusiness
FROM dbo.HolidayDefinition h
CROSS APPLY (
    SELECT
        BOM = CONVERT(DATE, CONVERT(CHAR(4), @Year) + RIGHT(CONVERT(CHAR(5), (100 * h.MonthNbr) + 10001), 4)),
        EOM = CASE h.WeekNbr
            WHEN 5 THEN DATEADD(DAY, -1, DATEADD(MONTH, 1, CONVERT(DATE, CONVERT(CHAR(4), @Year) + RIGHT(CONVERT(CHAR(5), (100 * h.MonthNbr) + 10001), 4))))
        END
) m
CROSS APPLY (
    SELECT
        offset = CASE h.WeekNbr
            WHEN 0 THEN h.DayNbr - 1
            WHEN 5 THEN CASE
                WHEN DATEPART(WEEKDAY, m.EOM) >= h.DayNbr THEN -(DATEPART(WEEKDAY, m.EOM) - h.DayNbr)
                ELSE -((DATEPART(WEEKDAY, m.EOM) - h.DayNbr) + 7)
            END
            ELSE CASE
                WHEN h.DayNbr >= DATEPART(WEEKDAY, m.BOM) THEN (h.DayNbr - DATEPART(WEEKDAY, m.BOM)) + (h.WeekNbr - 1) * 7
                ELSE (h.DayNbr - DATEPART(WEEKDAY, m.BOM)) + (h.WeekNbr * 7)
            END
        END
) o
CROSS APPLY (
    SELECT
        HolidayOn = DATEADD(DAY, o.offset, CASE h.WeekNbr
            WHEN 5 THEN m.EOM
            ELSE m.BOM
        END)
) r
WHERE h.CountryCd = @CountryCd
    AND (h.IsFederal = 1 OR h.IsBusiness = 1)
    AND h.Holiday NOT IN ('Good Friday', 'Easter Sunday')
UNION ALL
SELECT
    h.CountryCd,
    h.Holiday,
    r.HolidayOn,
    r.MonthNbr,
    WeekNbr = 0,
    r.DayNbr,
    h.IsFloating,
    h.IsFederal,
    h.IsBusiness
FROM (
    SELECT
        Holiday = 'Good Friday',
        HolidayOn = CONVERT(DATE, CONVERT(CHAR(4), @Year) + CASE
            WHEN e.d > 31 THEN '04'
            ELSE '03'
        END + RIGHT(CONVERT(CHAR(3), 100 + CASE
            WHEN e.d > 31 THEN e.d - 31
            ELSE e.d
        END - 2), 2)),
        MonthNbr = CASE
            WHEN e.d > 31 THEN 4
            ELSE 3
        END,
        DayNbr = CASE
            WHEN e.d > 31 THEN e.d - 31
            ELSE e.d
        END
    FROM (
        SELECT
            d.g,
            d.c,
            d.h,
            d.i,
            d.d,
            IsFalse = CONVERT(BIT, 0)
        FROM (
            SELECT
                c.g,
                c.c,
                c.h,
                c.i,
                d = c.i - ((@Year + (@Year / 4) + c.i + 2 - c.c + (c.c / 4)) % 7) + 28
            FROM (
                SELECT
                    b.g,
                    b.c,
                    b.h,
                    i = (b.c - (b.c / 4) - ((8 * b.c + 13) / 25) + 19 * b.g + 15) % 30
                FROM (
                    SELECT
                        a.g,
                        a.c,
                        h = (a.c - (a.c / 4) - ((8 * a.c + 13) / 25) + 19 * a.g + 15) % 30
                    FROM (
                        SELECT
                            g = @Year % 19,
                            c = @Year / 100
                    ) a
                ) b
            ) c
        ) d
    ) e
) r
CROSS APPLY (
    SELECT *
    FROM dbo.HolidayDefinition d
    WHERE d.CountryCd = @CountryCd
        AND d.Holiday = r.Holiday
        AND (d.IsFederal = 1 OR d.IsBusiness = 1)
) h;

With this function, you can generate a custom calendar with holidays and business days for any country and year.

By creating a dynamic holiday calendar in SQL Server, you can simplify the process of maintaining holiday and working day information. This solution can be customized to fit the specific rules and requirements of different countries and regions. Now you can say goodbye to the headache of manually updating holiday dates every year!