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