No Table? No Problem (January 2003)
The problem I presented in January involved calculating the number of working days between two given dates. I defined working days as non-weekend days—that is, Monday through Friday. This definition of working days wasn't very realistic because it didn't account for holidays or other nonworking days. However, it was sufficient for the scenario I wanted to present, which was how to perform calculations without accessing tables.
Korovin's solution to that problem involves accessing an auxiliary table but has minimal I/O costs. This efficient solution accommodates more-realistic scenarios that include holidays, for example.
Korovin's idea is to use an auxiliary Calendar table that contains a row for each date within a period of interest. A period of interest spans all from and to dates included in your applications' requests for calculating working days. You need the Calendar table to include all from and to dates that appear in your requests because the working days are calculated as the difference between the value of the total number of working days that is stored in the to date and the value stored in the from date. Each row includes the date and another column called wdc, which contains the total number of working days between a base date and the row's date. Run the code that Listing 6 shows to create the Calendar auxiliary table and populate it with dates in the range January 1, 1900, through December 31, 2999, the example period of interest.
A variable called @wdc keeps track of the number of working days that have passed between the base date and the loop's date counter. The loop code increments the counter only if the date counter represents a working day. The code in Listing 6 assumes a day is a working day only if the day of the week is Monday through Friday. However, you could add logic to identify and treat holidays or other days as nonworking days and not increment @wdc in such cases.
Finally, you create the dbo.fn_working-days() UDF to calculate the number of work-ing days between two given dates as follows:
CREATE FUNCTION dbo.fn_workingdays
(@d1 AS datetime, @d2 AS
datetime) RETURNS int
AS
BEGIN
RETURN((SELECT wdc FROM Calendar
WHERE cday = @d2)
- (SELECT wdc FROM Calendar
WHERE cday = @d1 - 1))
END
The function simply subtracts the wdc value of the day before the specified from date from the wdc value of the specified to date. To test the function, run the following query:
SELECT dbo.fn_workingdays('20000101', '29991231')
Notice how quickly you get the answer, 260887—that is, the number of working days between January 1, 2000, and December 31, 2999.
This function is so efficient because no matter which two dates you provide as arguments, the function invokes two queries that perform an index seek to fetch a value for each date, then calculates the difference—requiring very little I/O. On my laptop, the function runs for 8ms when the data isn't in cache and runs for less than 1ms when the data is in cache. In January, I presented a couple of solutions that don't involve any physical I/O. Those also run for less than 1ms, but they don't have the flexibility of accommodating holidays and other nonworking days.
Using auxiliary tables is a powerful way to simplify your solutions. The best solutions to T-SQL problems are often the simple ones. Simple solutions are often efficient as well as easy to understand and easy to maintain.