Calculate Business Hours

I have been writing stored procedures to do reports for various projects for as long as I have worked with SQL Server. And one thing that always gave me trouble was calculating the amount of business days (or hours) that had passed.

A great example is an order fulfillment aging report. Your boss wants to see all the orders that are more than 6 business hours old because your Service Level Agreement (SLA) states you will have all orders out the door in <= 6 hours. Your normal work day is 9 AM to 5 PM, Monday though Friday. So how do you do it? An order placed Friday night at 8 pm and shipped Monday at 12 pm is 64 hours old, but only 4 business hours old. So how do you calculate that?

I looked in a lot of places and found some "unique" methods of calculating this before I finally built my own. This function is pretty straight forward and I am sure it can be improved upon greatly, but it should give you a good idea of where to start.

One thing I need to do is add a parameter to set business days and hours. Right now it is hard coded for M-F from 8 am till 4 pm. Please feel free to use or modify this code as you might need. All code examples are presented AS IS and should be used at your own risk.


CREATE FUNCTION [dbo].[svf_CalcBusinessHours]
(
@StartDate DATETIME,
@EndDate DATETIME
)

RETURNS DECIMAL(10,2)

AS
/************************************************************************************************
Created 8/21/2008 by Jim Youmans. Code provided as is.

This function takes a start and end date and then counts the minutes based on
the M-F workday of 8 AM till 4 PM. Returns the number of hours in decimal
form (10,2).

*************************************************************************************************/

BEGIN

DECLARE @bFlag INT
DECLARE @TotalMinutes DECIMAL(10,2)
DECLARE @TotalHours DECIMAL(10,2)

SET @TotalMinutes = 0
SET @bFlag = 0

WHILE @bFlag = 0
BEGIN

IF (DATEPART(dw,@StartDate) >= 2) AND (DATEPART(dw,@StartDate) <= 6) BEGIN IF (DATEPART(hh,@StartDate) >= 8) AND (DATEPART(hh,@StartDate) < totalminutes =" @TotalMinutes">= @EndDate

SET @bFlag = 1

SET @StartDate = DATEADD(n,1,@StartDate)

END

SET @TotalHours = (@TotalMinutes / 60.0)
RETURN @TotalHours

END