SQL Server Magazine

To populate the Calendar table with sample data, run the code that Listing 2 shows.

You can use the following code to modify the UDF that calculates working days. Here's how the body of the first set-based version should look:

ALTER FUNCTION dbo.fn_workdays
(@d1 AS datetime, @d2 AS datetime)
  RETURNS int
AS
BEGIN
RETURN(SELECT COUNT(*) FROM Calendar
WHERE cday BETWEEN @d1 AND @d2 AND day_type & 1 = 0)
END

To test this version, replace the function from Listing 1 with the one above, specifying ALTER FUNCTION instead of CREATE FUNCTION. The first set-based UDF version runs for 1 second on my laptop given the arguments '20000101', '39991231'.

If you have to work with a Calendar table and you don't have a way to distinguish between the different types of non-working days, you can use the DATEPART() function to determine which dates fall on weekends. Here's the body of a second set-based version of the UDF:

RETURN(SELECT COUNT(*) FROM Calendar
  WHERE cday BETWEEN @d1 AND @d2
    AND DATEPART(weekday, cday + @@DATEFIRST) NOT IN(1, 7))

Using the same arguments as in the previous tests, the second set-based UDF version runs for 2 seconds on my laptop.

If you don't have a Calendar table in your database but you do have an auxiliary table with a large sequence of integers such as the one that Listing 3 creates, you can use the following set-based UDF, which performs about as well as the second one:

-- Assuming an auxiliary table 
-- called Nums exists with a 
-- column called n
RETURN(SELECT COUNT(*) FROM Nums
  WHERE n <= DATEDIFF(day, @d1, @d2) + 1
    AND DATEPART(weekday,
(@d1+n-1) + @@DATEFIRST) NOT IN(1,7))

Now let's examine the single-expression approach to solving the problem.

Prev. page     1 2 [3] 4 5     next page