CODEWITHSUNDEEP
sqlsql-server
The Fool
The Fool

Reputation: 20625

SQL Function times out with specific input (while loop)

I want to get the first business day of a given month. I generate a holiday table and use it in combination with regular weekday check, to find the first business day of a month. It seems to work. However, when I use the first month as input e.g.(2020, 1) the function times out. This is for every year like this, every other month works.

CREATE FUNCTION FirstBusinessDay(@year INT, @month INT) RETURNS DATETIME
AS BEGIN
    DECLARE @calendar TABLE (Name varchar(80), Date DATETIME)
    INSERT INTO @calendar SELECT * FROM dbo.HolidayTable(@year)
    DECLARE @d DATETIME = DATEFROMPARTS(@year, @month, 1)
    DECLARE @isHoliday bit
    SELECT @isHoliday = CASE WHEN Name IS NULL THEN 0 ELSE 1 END
    FROM @calendar WHERE Date = @d
    WHILE DATEPART(DW, @d+@@DATEFIRST-1) > 5 or @isHoliday = 'true'
    BEGIN
        SET @d = DATEADD(DAY, 1, @d)
        SELECT @isHoliday = CASE WHEN Name IS NULL THEN 0 ELSE 1 END
        FROM @calendar WHERE Date = @d
    END
    RETURN @d
END;
GO

Example usage

select dbo.FirstBusinessDay(2020, 1) as 'First Workday';  -- timeout
select dbo.FirstBusinessDay(2020, 2) as 'First Workday';  -- works 
select dbo.FirstBusinessDay(2020, 3) as 'First Workday';  -- works
select dbo.FirstBusinessDay(2021, 7) as 'First Workday';  -- works
select dbo.FirstBusinessDay(2020, 12) as 'First Workday'; -- works
select dbo.FirstBusinessDay(2021, 1) as 'First Workday';  -- timeout
select dbo.FirstBusinessDay(2022, 1) as 'First Workday';  -- timeout

As you can see, there is a pattern, every time the first month is used, it times out. Can anyone spot the issue?

Upvotes: 0

Views: 109

Answers (1)

Clockwork-Muse
Clockwork-Muse

Reputation: 13106

The remarks section about setting variables this way says:

If the SELECT statement returns no rows, the variable retains its present value.

... so what's happening is pretty clear: New Year's Day is a holiday, and sets @isHoliday to true. Since there are no rows in @calendar that are not holidays, it can never be set to false. The other months only work because the default of @isHoliday is 0 - false.

That said, what you really want is a true calendar table, one listing every date, with a bunch of other indexable columns. It turns your function into a simple query:

SELECT MIN(calendar_date)
FROM Calendar
WHERE calendar_date >= DATEFROMPARTS(@year, @month, 1)
      AND is_holiday = 'false'
      AND day_of_week_iso < 6

Calendar tables are probably the most useful tables for dimensional analysis, especially for various aggregates (eg, "every sale grouped by month"), because it turns them into ranged bounds queries, instead of needing to use something like DATEPART.

Upvotes: 2

Related Questions