SQL Server Magazine

The Single-Expression Approach
For the single-expression approach, you write one expression that doesn't access any tables to calculate the working days in the given range. You could reasonably assume that, if successful, this approach would perform better than the other two because it doesn't involve iterations or physical I/O (because no data needs to be read from disk). Let's look at two different solutions that use the single-expression approach. The first solution uses the following algorithm to calculate the number of working days between the two given dates @d1 and @d2:

(Number of whole weeks in the range @d1, @d2) * 5 +
  (Number of remaining days in last non-whole week
   that don't fall on Saturday or Sunday)

Note that for the purpose of this algorithm, a whole week starts on the weekday @d1. To calculate the number of whole weeks in the range @d1 to @d2, use the DATEDIFF() function to calculate the number of days in the range and divide the result by 7. Because SQL Server is using integer division, the fraction is truncated. Multiply the resulting number of whole weeks by 5, as the following code shows:

(DATEDIFF(day, @d1, @d2) + 1) /
7 * 5

and you get the number of working days in the whole weeks in the range.

Now you have to calculate the number of working days in the remaining days in the non-whole week. I'm going to cheat a little here and use a set-based operation against a small derived table that I create from scratch. See if you can work out what the following query calculates:

SELECT COUNT(*)
FROM (SELECT 0 AS n UNION ALL
SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 3 
UNION ALL SELECT 4
  UNION ALL SELECT 5) AS RW
WHERE n < (DATEDIFF(day, @d1, 
    @d2) + 1) % 7
  AND DATEPART(weekday, @d2 - n + @@DATEFIRST) NOT IN(1, 7))

Of the 6 values (0-5) in the RW derived table, the first logical expression in the WHERE clause uses the modulo operator (%) as a filter to give you only the values that are less than the number of days in the remaining non-whole week. For example, given the range '20030101', '20030110', which spans 10 days, the first logical expression in the WHERE clause can be simplified to n < 10 % 7, or n < 3. Only three rows qualify (n = 0, 1, 2). The second logical expression further filters the result by subtracting n from @d2 and checking that the result doesn't fall on Saturday or Sunday. Add the number of working days in the remaining non-whole week to the number of working days in the whole weeks, and you get the desired result. Listing 4 shows the first UDF version that uses the single-expression approach.

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