In many previous articles, I've emphasized the strength of the set-based approach to solving T-SQL problems. For most of the problems I discussed, I provided one or more tables and asked you to write code to produce a certain output. There are two possible approaches to solving problems involving data that's stored in tables: iterative and set-based. The iterative approach uses cursors, temporary tables, and loops to iterate through rows. This approach usually requires lots of code and focuses more on how to get the data than on what data you want.
Conversely, with the set-based approach, you write a query that logically specifies the request, letting SQL Server handle the query implementation. In other words, you specify what you want, and SQL Server decides how to get it. Usually, set-based solutions perform better than the iterative alternatives because SQL Server's query optimizer can choose among several possible execution plans for the same query.
Some T-SQL problems don't necessarily involve data that's stored in tables—rather, just data supplied as arguments. These problems require a third type of solution—one that uses one expression that's based on pure logic. In terms of performance, a single-expression approach is usually superior to both the set-based and the iterative approaches.
Let's look at a problem I call the working-days problem and explore the three types of solutions to it. I'd like to thank SQL Server trainers Dejan Sarka, Bruno Reiter, Luca Bianchi, David Lundell, and Dieter Nöth, who provided different solutions to a similar puzzle I presented in a private trainers forum. I've incorporated their ideas into the solutions I discuss here.
The working-days problem is simply this: Write a function that accepts two dates as input arguments and calculates the number of non-weekend dates between them. (In the United States, for example, weekend dates fall on Saturday or Sunday.) The range is inclusive—that is, it includes the two given input dates. For example, if a job started on Monday, January 6, 2003, and ended on Friday, January 17, 2003, it took 10 working days in total. Before reading the following solutions, try to come up with the most efficient solution you can.
The Iterative Approach
The iterative approach is the simplest to implement but the worst in terms of performance. You create a simple loop that iterates through all dates between the two given dates, incrementing a counter only if the current date doesn't fall on a weekend. Run the script that Listing 1 shows to create the fn_workdays() user-defined function (UDF), which implements an iterative approach.
Note that the fn_workdays() function assumes that DATEFIRST is set to 7 (US English default), in which case the DATEPART() function that takes the weekday argument returns 1 for Sunday and 7 for Saturday. If DATEFIRST is set to a different value in your environment, you need to specify the appropriate values instead of 1 and 7 or neutralize the effect of the DATEFIRST setting by adding @@DATEFIRST to the given date. Setting DATEFIRST to 7 and invoking
DATEPART(weekday, <some_date>)
gives you the same result as invoking
DATEPART(weekday,
<some_date> + @@DATEFIRST)
regardless of the DATEFIRST setting.