A Bulk Approach to Business Age Calculation

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.

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.

Step 2: Calculate the number of Saturdays and Sundays (SS) by multiplying the number of weeks between DAY1 and DAY2 by two.

SS=2*DATEDIFF(wk,DAY1,DAY2)

Note that weekends and weekdays are based on country settings; my example uses US weekends.

Step 3: Find the number of holiday days (HD) between DAY1 and DAY2, excluding any holidays that happen to fall on Saturday or Sunday.

HD=(SELECT COUNT(*) FROM  #tmpHolidays WHERE hol_date BETWEEN DAY1 and DAY2 AND DATEPART (dw, hol_date)   NOT IN (1,7))

Step 4: Add one day to the calculation if DAY1 was Saturday (S1), because Step 2 already included DAY1.

S1=CASE WHEN DATEPART(dw,DAY1)=7   THEN 1 ELSE 0 END

Step 5: Subtract one day from the calculation if DAY2 was Saturday (S2), because Step 2 already included DAY2.

S2=CASE WHEN DATEPART(dw,DAY2)=7   THEN 1 ELSE 0 END

The final logical formula to calculate the business age is BA=CD-SS-HD+S1-S2. The code in Listing 3 employs this formula to add a calculated business age column to Table 1. Table 3 shows the new table with the business age column included.

The code in Listing 4 builds on the previous calculation to retrieve all records in which the business age (i.e., the number of business days elapsed between DAY1 and DAY2) is greater than 10. Table 4 shows this derived data.

User-Defined Functions

As I mentioned previously, advanced developers can employ UDFs to incorporate the code for calculating business age into one routine to use elsewhere. This approach works only for SLQ Server 2000 and later, with a minimum database compatibility level of 80. In addition, the UDF option doesn't work with temporary tables. To use the code in my examples, you need to create permanent tables called tmpTable and tmpHolidays. Creating these tables is a simple matter of dropping the # character from the code in Listings 1 and 2. (This character designates a temporary table.)

Listing 5 contains the code to create the UDF business_age. The simple command SELECT * FROM business_age(30) is used to select records in which the business age is greater than 30. Table 5 shows the resulting calculation.

The Bulk Advantage

Finding the business age between two dates stored in database is a common task for developers. The bulk method that I suggest is a creative solution that uses just a single SELECT command. This solution has three major advantages. First, the code is exceedingly straightforward—nothing beats a single SELECT statement for simplicity. Second, no application front-end code is necessary; you can use pure T-SQL commands to complete the task. And third, network traffic is kept to a minimum because only aged records pass from the server to client for further processing