For the past four months, I've discussed various calculations involving datetime
data. This month, I conclude the series by introducing techniques to calculate
working days, age, and next birthday date. Later in the article, I also point
you to additional resources where you can find more information about temporal
data manipulation.
Calculating Working Days
To tackle this problem, we can write a function called fn_workingdays that calculates
the number of working days in an input period. The function accepts three input
parameters:
- @from_dt: start date of period
- @to_dt: end date of period
- @datefirst: first working day of the week (e.g., 1 for Monday, 2 for Tuesday,
3 for Wednesday)
The output of the function is an integer representing the number of working
days in the input period. Working days are weekdays 1 through 5, based on the
input first working day of the week (@datefirst). For example, if @datefirst
is set to 1 (Monday), Monday through Friday are considered working days, whereas
Saturday and Sunday are considered nonworking days. If @datefirst is set to
7 (Sunday), Sunday through Thursday are considered working days, whereas Friday
and Saturday are considered nonworking days.
Note that the recommended approach to handle such calculations is to maintain
an auxiliary table of dates, with attributes signifying whether a certain date
is a working day. This approach is flexible because it can also take holidays
and other special cases into consideration. However, for the purpose of this
discussion, we'll narrow the problem to a simpler case in which you need to
take into consideration weekdays 1 through 5 (based on the given first working
day of the week)—not weekdays 6 and 7. We won't handle holidays and other
special cases. This problem is solvable with a single expression that doesn't
involve the need to query any auxiliary tables.
You can find my suggested solution—which is, of course, only one of
many options—in Web Listing 1,
http://www.sqlmag.com, InstantDoc ID 95675. (You might try solving the problem
on your own before looking at my solution.) The logical approach I've taken
is as follows:
- Calculate the number of whole weeks in the period and multiply by 5 (working
days)
- Add the number of remaining days in the part of the week left at the end
of the period (can be 0 through 6 days)
- Subtract 1 day if weekday 6 is within the remaining part of the week
- Subtract 1 day if weekday 7 is within the remaining part of the week
As for the more technical description
of the function's definition, I'll start with
the query defining the innermost derived
table D1 and go outward. The query
defining D1 calculates two columns—
days, which is the number of days in the
input period, and from_weekday, which
is the weekday number of @from_dt
based on the input first working day of
the week (@datefirst). The calculation
of days is straightforward: using a simple
DATEDIFF function that calculates the
difference in terms of days between @
from_dt and @to_dt, plus one. As for the
calculation of from_weekday, by now you
should be comfortable with this calculation of a language-independent weekday
number, which I explained in "DATETIME Calculations, Part 2," InstantDoc
ID 94819.
The purpose of the query defining D2 is to calculate the to_weekday
column: from_weekday plus the number of days in the remaining part of the week,
minus 1. Note that to_weekday can be higher than 7, allowing for simpler calculations
in the outermost query. For example, if from_weekday is 5 and there are 4 days
in the remaining part of the week, to_weekday will be 8. Therefore, figuring
out whether days 6 and 7 are within the remaining part of the week at the end
of the period is simple.
The outermost query then performs the final calculation of the number of working
days. As I described earlier, the result amounts to the number of whole weeks
times 5 (days/7*5), plus the number of days in the remaining part
of the week (days%7), minus 1 if 6 is within the input period (6 between
from_weekday and to_weekday), minus 1 if 7 is within the input period (7 between
from_weekday and to_weekday).
To verify that the calculation is correct, let's test the function. Run the
code in Web Listing 1 to create it. (Make sure you're connected to the database
in which you want the function to be created—for example, tempdb.) Next,
run the following code:
SELECT dbo.fn_workingdays
(‘20070212', ‘20070223', 1)
This period covers 12 days (starting on a Monday and ending on a Friday), two
days of which are nonworking days. (Monday is set as the first working day of
the week.) You get 10 working days back, as expected. Next, run the following
code:
SELECT dbo.fn_workingdays
(‘20070212', ‘20070223', 7)
It's the same period as before, but now three days within the period are nonworking
days. (Sunday is set as the first working day of the week.) You get nine working
days back, as expected.
Prev. page
[1]
2
next page 
