I have a calendar table with a record for every date I need to care about (i.e. 365 records per year), with various meta-information about each date, including the weekday, the fiscal calendar week, month, quarter and year it's under, whether there's a Holiday on that date, etc.
The table has the following keys / indexes:
,constraint PK_FiscalCalendars primary key clustered (_Id) ,constraint NK_FiscalCalendars unique (CalendarDate)
create nonclustered index IX_FiscalCalendars_HolidayFiscalDayOfWeek on dwd.FiscalCalendars (Holiday, CalendarDate, FiscalDayOfWeek);
That's nice, but now I need to use this calendar table in various queries, and one of my requirements is to be able to determine the number of workdays between two dates, taking into account the contents of the
Holiday column, which contains a non-null
nvarchar(50) value (the name of the Holiday / reason for closing) whenever a weekday/date shouldn't be considered a workday; the column contains a
NULL value otherwise.
So I've implemented this little function that intakes two dates and returns the number of workdays between them (inclusively):
create function dbo.WorkWeekDateDiff(@fromDate date, @toDate date) returns int as begin /* returns the number of workdays (non-holiday week days) between two dates. */ declare @result int; with calendar as ( select CalendarDate from dwd.FiscalCalendars where CalendarDate between @fromDate and @toDate and Holiday is null -- exclude holidays and FiscalDayOfWeek not in (1,7) -- exclude Sunday and Saturday ) select @result = (select count(*) from calendar); return @result; end
Execution plan is showing 73% of the cost going on seeking the non-clustered index, and 27% performing that
Count(*). It gets the job done, but since this is a scalar-valued function I'd like it as efficient as possible. Is there anything to improve?