Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Function : Find ‘X’ Business Days in the Future

By J Gravelle,

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 2nd. But a six-day deadline wouldn’t come due until January the 5th.

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:

fn_AddBizDays process flow


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:

fn_AddBizDays1.jpg

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:


We could feasibly use a decimal in place of an integer if, for whatever reason, we wanted to determine deadlines for fifty-seven and a half days into the future, but since DATEADD() only accepts integers for arguments, there’d be some parsing of partial days into hours involved.

I can foresee a demand for this modification/enhancement, since many SLAs are now written against billable hours rather than days.

Which means an hour-handling version of this function is probably my next project…

Total article views: 10023 | Views in the last 30 days: 9
 
Related Articles
SCRIPT

T-SQL Function to Determine Holidays in SQL Server

T-SQL Function to Determine Holidays in SQL Server

FORUM

Holidays

I need to find a way to skip holidays for a scheduled job.

SCRIPT

Holiday List

A table UDF to return a list of holidays for a given year.

FORUM

T-SQL Function to Determine Holidays in SQL Server

Comments posted to this topic are about the item [B]T-SQL Function to Determine Holidays in SQL Serv...

ARTICLE

Creating Holidays and Special Dates

A way to save holiday records that can be created as needed into a search-able calendar table.

Tags
business days    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones