Working Days DateDiff function

Question

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)

and:

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?

Answer 1

You have a physical table that has been established for the purpose of managing calendar dates. This is great, but with just a little more work it can be extended to do so much more.

Consider adding a column "DayType", with values like:

0 -> Normal 1 -> Weekend 2 -> Holiday

You could add this easily... right?

Then, you can maintain it with:

update FiscalCalendars
set DayType = case
                  when Holiday is not null then 2
                  when FiscalDayOfWeek in (1, 7) then 1
                  else 0
              end

Now your query becomes quite simple:

select count (*)
from FiscalCalendars
where CalendarDate between @fromDate and @toDate
  and DateType = 0

Index the column with (DateType, CalendarDate)

Maintaining this static data in a function would be a poor use of computational power, and make indexing harder.

Your function would thus become:

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;

    select @result = (select count(*)
                      from dwd.FiscalCalendars
                      where DateType = 0
                        and CalendarDate between @fromDate and @toDate
    );

    return @result;

end