SQL Function : Find ‘X’ Business Days in the Future
Calculation for Determining  SLA Deadlines

My clients’ reporting needs often require determining whether cases stored in their proprietary CRM are staying within the parameters of the various Service Level Agreements that they are bound by.

For example, if a case was opened on Christmas Eve Day (12/24/2008) and my client was contractually obligated to resolve the issue within 5 business days, the SLA deadline would be January 2^nd. But a six-day deadline wouldn’t come due until January the 5^th.

These variances aren’t readily apparent. And what if we need to determine fifty days out? Since my client’s business process variables can change the SLA interval required for different tasks, I needed a way to directly determine on demand whether a given project was within SLA or not.  My fn_AddBizDays function does just that:

fn_AddBizDays(dtBeginDate, iBizDays) -> returns the deadline date as a DateTime variable type.

The process is simple. We use a single integer variable:

• iBizDays
  – supplied by the user as an argument to the function, representing X biz days hence

…and these three variables of a DateTime value type:

• dtBeginDate
  – supplied by the user as an argument to the function;
• dtEndDate – the
  calculated datestamp returned to the calling SQL; and
• dtDateHolder
  – serving as a temporary value holder during the calculation.

Note that dtBeginDate could simply have been overwritten again and again during the iterations of this function, but I’d like to keep that original value intact for extensibility and flexibility, in case we want to build onto this process later.

The logic is simple enough. We add X number of iBizDays to dtBeginDate to create our initial dtEndDate. Using dtDateHolder as our temporary date holder, we can then cycle through each date and, in the event a particular date is a Saturday, Sunday, or a holiday, increment dtEndDate by one day, and continue through the entire sequence of days:

Determining if a Day is Saturday or Sunday

SQL is nice enough to allow us to retrieve the day of the week as a number value represented by Sunday (1) through Saturday (7). So this function:

DATEPART(dw, @dtDateHolder) in (1, 7)

…will return as true if our currently tested date is a weekend.

Determining if a Day is a Holiday

This simple table can be referenced to determine if the currently tested date is a company holiday:

Note that using the reserved “description” is this client’s pre-existing labeling standard:

Just remember to square-bracket it if you use it in your queries, e.g.: dbo.tblHolidays.[description]

One last little trick: We need to disregard timestamps when comparing against the holiday table, because obviously

01/01/2008 12:00:00 does NOT equal 01/01/2008 08:15:00

So we use the DateDiff function to zero out our timestamp, like this:

DATEDIFF(dd, 0, @dtDateHolder)

…on the safe assumption that any given holiday will last the entire day.

At this point, there’s nothing left to do but put all these pieces together!

–Usage example:
–SENT : dbo.fn_AddBizDays(’12/24/2008', 2)
–RECD: 2008-12-29 00:00:00.000
–(12/25 is a Holiday, 12/27 & 12/28 are Saturday & Sunday)

CREATE function fn_AddBizDays
–Accepts Date ‘X’ and Integer ‘Y’

(
@dtBeginDate datetime,
@iBizDays int
)

RETURNS datetime
–Result is Date ‘Z’ that is ‘Y’ business days after supplied Date ‘X’

AS

BEGIN

DECLARE @dtEndDate datetime
DECLARE @dtDateHolder datetime

SET @dtDateHolder = @dtBeginDate
SET @dtEndDate = DATEADD(d,@iBizDays,@dtBeginDate)

WHILE (@dtDateHolder <= @dtEndDate)
BEGIN

–Is the date being checked a Saturday or Sunday?
IF((DATEPART(dw, @dtDateHolder) in (1, 7))
–Is the date being checked a holiday?
OR (DATEADD(dd, 0, DATEDIFF(dd, 0, @dtDateHolder))
in (SELECT holiday_date FROM tblHolidays)))
–NOTE: DATEDIFF trick used above to discard TIMESTAMP

BEGIN

– Extend the date range for Weekends and Holidays by one day
SET @dtEndDate = DATEADD(d,1,@dtEndDate)

END

–Move to the next day in the range and loop back to check it
SET @dtDateHolder = DATEADD(d,1,@dtDateHolder)

END

–Respond with newly determined end date
RETURN @dtEndDate

END

The function can be called directly:

SELECT [dbo].[fn_AddBizDays](’12-24-2008', 5)

…or embedded in a calling procedure as a sub-select for greater flexibility, as I’ll show you in my real-world example, next.

I used this solution for a client company whose employees are required to determine and assign a deliverable date to a case within two business days of the creation of a request. Management needed to generate a daily report as an enforcement mechanism, in the event an employee failed to estimate a deliverable date as required within the allotted time span.

While there are more elegant trigger-driven/email generating solutions, all management wanted was a quick and easy daily “Slacker Report” generator, which I gave them by embedding a call to the following stored procedure in an Excel spreadsheet:

SELECT c.case_id, c.title, c.create_date,
(SELECT dbo.fn_AddBizDays( c.create_date, 2)) as due4dd,
c.delivery_date, s.description AS status, u.username
FROM case_history h INNER JOIN
cases c ON case_history.case_id = cases.case_id INNER JOIN
status s ON case_history.status_id = status.status_id INNER JOIN
users u ON case_history.technician_id = users.user_id
WHERE (dbo.fn_AddBizDays( cases.create_date, 2) > GetDate())

…which you’ll notice uses the function twice: once to determine a deadline date for display, and again as a results limiter, since the boss only wants to yell at people whose deadline has passed. Eliminating the WHERE clause would expose techs whose deadline was encroaching as well, if needed: