Reputation: 20625
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
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