Calculating the number of work days elapsed between two dates (i.e., the business
age) is one of the most common tasks in business application development. In
many situations, this computation plays a significant and sometimes crucial
role in the application logic. For example, suppose that a company promises
to
ship orders within five business days from the payment date, and your assignment
is to find all the orders that shipped later than this constraint. In other
words, you must find all the orders in which more than five business days elapsed
between the payment date and the ship date.
The common approach to this problem is to use a single-row method. This approach
calculates the business age for only one row in a table at a time and compares
the result with the required age. Then the code scrolls to the next record and
repeats the calculations. This method is typically implemented either within
application front-end source code (e.g., Visual Basic—VB, Delphi, C#)
or by using more complicated T-SQL routines that loop through a data set, fetch
a row, and calculate the business age. Although these routines are workable
solutions, they have major drawbacks, such as increased network traffic to submit
all rows from a SQL Server machine to a client for further processing rather
than simply submitting a set of data that meets the business age requirements.
In addition, SQL Server stored procedures introduce unnecessary complexity because
you must create a loop to check every record to determine whether the records
meet your criteria.
In this article, I offer a solution that uses only one SELECT command for all
the rows in a data set. This bulk approach is both simple and elegant. Note
that the examples and formulas I use are for US date calculations. Readers outside
the United States will need to modify my approach. (For more information about
working days calculation, see "DATETIME Calculations, Part 5," June 2007, InstantDoc
ID 95675.)
Solution
The first step is to write a T-SQL SELECT statement to show business age as
a calculated column in a SELECT clause. Then, you need to write a statement
that incorporates business age into a WHERE clause to include only rows that
meet certain aging criteria. Advanced developers can also incorporate the logic
into a user-defined function (UDF). This solution lets you write a single routine
that you can call anytime you need the calculations.
Weekend identification (i.e., Saturday and Sunday) is based on a number that
corresponds to the day of the week. This number depends on the value set by
the SET DATEFIRST command, which sets the first day of the week. For US regional
settings, Sunday is the first day of the week, Monday is the second day of the
week, and Saturday is the seventh day of the week.
A company's holidays might be unique; therefore, few businesses use the same
holiday schedule. The most common approach for dealing with holidays is to create
a simple table in which to store holiday dates.
To use the solution I suggest, you must be familiar with several built-in T-SQL
date functions, such as the following.
- DATEPART(wk, date)—Returns a number that corresponds to the
day of the week (e.g., Sunday=1, Saturday=7)
- DATEDIFF(day, startdate, enddate)— Returns the number of days
between startdate and enddate.
Create the Necessary Tables
First, use the code in Listing 1 to create
a temporary table named #tmpTable and insert the specified dates. (Note that
I use the same dates throughout the article.) Table
1 shows the temporary table that Listing 1 creates.
Next, use the code in Listing 2 to create
a temporary holiday table named #tmpHolidays. This code inserts just two dates,
for test purposes only. Table 2 shows the temporary
holiday table that Listing 2 creates.
Calculate the Business Age
Next, calculate the business age. The algorithm to calculate business age can
be broken down into 5 simple steps.
Step 1: Calculate the number of calendar days (CD) between DAY1
and DAY2.
CD=DATEDIFF(day,DAY1,DAY2)
Note that this calculation doesn't include both boundary dates.
Prev. page
[1]
2
next page 
