The second version of the UDF that uses the single-expression approach is even simpler. It relies on the fact that the DATEDIFF(week, @d1, @d2) function returns the number of week boundaries crossed between @d1 and @d2, considering Saturday as the last weekday and Sunday as the first, regardless of the DATEFIRST setting. Let wb equal the number of week boundaries crossed between @d1 and @d2. Logic says that the range @d1 to @d2 has at least 2*wb days that fall on Saturday and Sunday, plus another one if @d1 falls on Sunday and another one if @d2 falls on Saturday.
If you translated this logic to T-SQL, the body of the second UDF version that uses the single-expression approach would look like this:
RETURN(SELECT
DATEDIFF(day, @d1, @d2) + 1 -
2 * DATEDIFF(week, @d1, @d2) -
CASE WHEN
DATEPART(weekday,@d1+@@DATEFIRST)=1 THEN 1 ELSE 0 END -
CASE WHEN
DATEPART(weekday,@d2+@@DATEFIRST)=7 THEN 1 ELSE 0 END)
Both single-expression versions of the UDF complete in a fraction of a second regardless of the date range's size, demonstrating that this approach is superior to all others.
Test Your Skills
When you need to perform calculations that don't necessarily involve accessing data that's stored in tables, take the logical path to a solution that doesn't use iterations. To test your skills, see if you can devise a single-expression approach to solving the following problem. Given the table
CREATE TABLE Strnums
( sn VARCHAR(100) NOT NULL CHECK'
(/* your expression goes here */) )
write a logical expression for the CHECK constraint that allows only strings made up of digits in the sn column. Note that users might attempt to enter strings containing characters other than digits and the Latin characters a through z. You need to take all possible characters, including special ones, into consideration. For example, of the values 1234567890, 1a, $123, 12*34, 12,345, 1E3, and 12.34, only the first should be able to enter the table. The Web-exclusive sidebar "Solution to CHECK Constraint Puzzle" holds the answer to this challenge; just enter InstantDoc ID 27136 to check your solution against mine.
End of Article
Prev. page
1
2
3
4
[5]
next page -->
