CREATE FUNCTION dbo.CalcWorkHours (

@start_date DATETIME,

@end_date DATETIME)

RETURNS INT

AS

BEGIN

RETURN

(SELECT ((total_days / 7) * 5 + total_days % 7 -

CASE WHEN 6 BETWEEN start_weekday AND end_weekday

THEN 1 ELSE 0 END -

CASE WHEN 7 BETWEEN start_weekday AND end_weekday

THEN 1 ELSE 0 END) * 8

FROM (SELECT total_days, start_weekday,

start_weekday + total_days % 7 - 1

FROM (SELECT DATEDIFF(day, @start_date, @end_date) + 1,

DATEPART(WEEKDAY, @start_date + @@DATEFIRST - 1)

) AS T(total_days, start_weekday)

) AS D(total_days, start_weekday, end_weekday)

);

END

GO

SELECT dbo.CalcWorkHours('20080401', '20080421')

/*

-- Result

120

*/

## Tuesday, January 15, 2008

### Calculating Work Hours

Calculating work hours is a very frequent need in scheduling, billing, payroll, and time and attendance applications. Normally this is best done using an auxiliary Calendar table which provides easy way for calculation and flexibility to account for holidays and other events. However, sometimes the requirements are simplified and the point of interest is only the work hours between two dates, accounting only for weekends, without holidays. Here is one way to perform this calculation using the date and time functions in SQL Server. Note that for this example the week start day is logically set to Monday by adding (@@DATEFIRST - 1) to the start date.

Labels:
t-sql programming

Subscribe to:
Post Comments (Atom)

## 13 comments:

what should i change/add to this function to make it work exactly like this "select dbo.CalcBusinessHours('2011-06-03 11:00','2011-06-03 13:00') "

where there's only one holiday and that is 'saturday ' and business hour from 9am to 1pm .thank u

Roji,

First you have to replace "* 8" with "* 4" because your work is 4 hours. Then remove the "CASE WHEN 7 THEN ..." from the calculation because you do not treat Sunday as non-work day. That should do it.

Mr.Plame,

Thank you so much...I am still learning sql and trying to understand how to implement such a function.i have spent lotz of time searching about dateoff,datediff ,dateadd etc.but honesly i didn't find anything as good as urs.Anyways here what i am planning to do :

1- my business hours r from (9am to 6pm) , and -1 hour lunch break.

2- days of business : monday to saturday ,however saturday is a half day business , from 9am to 1pm.

3- function should be "select dbo.CalcBusinessHours('2011-06-03 9:00','2011-06-03 18:00') "

4- sunday is holiday.

Please try to help me with this.Thanks yours roji zn

Roji,

To solve this problem the best is to create a calendar table. See the following link for more details: http://pratchev.blogspot.com/2007/12/auxiliary-tables.html.

Here is one example with your requirements. In essence you populate the calendar table with sufficient number of days (10 years is a good start). Then assign the work hours for each day. Calculating the business hours between two dates becomes very easy because you just need to sum that work hours for all days in the range. The last query in my example below does that. You can create a function out of it but it is not necessary, you can just use the query as it is very simple.

IF OBJECT_ID(N'Numbers', N'U') IS NOT NULL

DROP TABLE Numbers;

CREATE TABLE Numbers (

nbr INT NOT NULL

CONSTRAINT df_numbers_nbr

DEFAULT 1

CONSTRAINT ck_numbers_nbr

CHECK (nbr > 0)

CONSTRAINT pk_numbers

PRIMARY KEY);

WITH

Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),

Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),

Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),

Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),

Nums (n) AS

(SELECT ROW_NUMBER() OVER(ORDER BY n)

FROM Num5)

INSERT INTO Numbers (nbr)

SELECT n FROM Nums

WHERE n <= 10000;

IF OBJECT_ID(N'Calendar', N'U') IS NOT NULL

DROP TABLE Calendar;

CREATE TABLE Calendar (

cdate DATETIME NOT NULL

CONSTRAINT df_calendar_cdate

DEFAULT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)

CONSTRAINT ck_calendar_cdate

CHECK (cdate = DATEADD(day, DATEDIFF(day, 0, cdate), 0))

CONSTRAINT pk_calendar

PRIMARY KEY,

cweekday INT NOT NULL

CONSTRAINT df_calendar_cweekday

DEFAULT 1

CONSTRAINT ck_calendar_cweekday

CHECK (cweekday BETWEEN 1 AND 7),

cday_name VARCHAR(9) NOT NULL

CONSTRAINT df_calendar_cday_name

DEFAULT 'N/A',

work_hours INT NOT NULL

CONSTRAINT df_calendar_work_hours

DEFAULT 0);

-- insert 10 years starting from Jan 1 2008

INSERT INTO Calendar (cdate)

SELECT DATEADD(dd, N.nbr - 1, '20080101')

FROM Numbers AS N

WHERE N.nbr <= 3653;

-- set days of the week

UPDATE Calendar

SET cweekday = DATEPART(dw, cdate + @@DATEFIRST - 1),

cday_name = DATENAME(dw, cdate);

-- set hours

UPDATE Calendar

SET work_hours = CASE cday_name

WHEN 'Saturday' THEN 4 -- 9am to 1pm

WHEN 'Sunday' THEN 0 -- not a work day

ELSE 8 -- 9am to 6pm with 1 hour break

END;

-- calculate business hours between 2011-06-01 and 2011-06-05

SELECT MIN(cdate) AS range_start,

MAX(cdate) AS range_end,

SUM(work_hours) AS business_hours

FROM Calendar

WHERE cdate BETWEEN '20110601' AND '20110605';

/*

range_start range_end business_hours

----------------------- ----------------------- --------------

2011-06-01 00:00:00.000 2011-06-05 00:00:00.000 28

*/

There is a commercial solution available for adding working days calculations and other useful functions to SQL Server that replicate and extend the functions available in MS Excel - see www.totallysql.com

How will you adjust this code if the dates include time portion (so you need to correctly calculate time for the non complete dates)?

Do you have any thought about how to include civic holidays to this logic as well? thx!

Reza,

The best is to create a calendar table. See the following link for more details: http://pratchev.blogspot.com/2007/12/auxiliary-tables.html. You can add the civic holidays to the calendar table and then calculation is a trivial query to sum the work hours between two dates.

Declare

@StartDate Datetime,

@EndDate Datetime,

@FirstDaySecDiff int,

@LastDaySecDiff int,

@CummulativeSecs int = 0,

@TotalSecs int = 0,

@WorkDaySecs int = 43200, --> 60 secs X 60 Minutes X 12 hours (8am to 8 pm)

@SaturdaySecs int = 14400, --> 60 secs X 60 Minutes X 4 hours (8am to 12 noon)

@LoopInt int,

@StartVariable int = 0,

@VariableDate datetime

set @StartDate = '9/19/2012 10:35:45'

set @EndDate = '9/19/2012 4:33 PM'

set @VariableDate = @StartDate

select @FirstDaySecDiff = DATEDIFF(ss,@StartDate, CONVERT(datetime,convert(varchar(8),@StartDate,112) + ' 20:00:00'))

select @LastDaySecDiff = DATEDIFF(ss,CONVERT(datetime,convert(varchar(8),@EndDate,112) + ' 08:00:00'),@EndDate)

--select @FirstDaySecDiff/3600.00

select @LoopInt = DATEDIFF(dd ,@StartDate,@EndDate) - 1

--select 60 * 4 * 60

if @LoopInt < 0

begin

select @LoopInt = 0

select @LastDaySecDiff = 0 -- (Same Day)

end

--select @StartVariable, @LoopInt

While @StartVariable < @LoopInt and @LoopInt <> 0

begin

if DateName(weekday,@VariableDate) = 'Saturday'

begin

select @CummulativeSecs = @CummulativeSecs + @SaturdaySecs

end

if DateName(weekday,@VariableDate) not in ('Saturday','Sunday')

begin

select @CummulativeSecs = @CummulativeSecs + @WorkDaySecs

end

select @StartVariable = @StartVariable + 1

select @VariableDate = @StartDate + 1

Continue

end

select @TotalSecs = isnull(@CummulativeSecs,0) + isnull(@FirstDaySecDiff,0) + isnull(@LastDaySecDiff,0)

DECLARE @retval VARCHAR(30);

SET @retval = cast(@TotalSecs/(60*60) as varchar(10))+':'+

cast( (@TotalSecs-@TotalSecs/(60*60)*3600)/60 as varchar(10))+':'+

cast( (@TotalSecs-@TotalSecs/(60)*60) as varchar(10));

select @TotalSecs as Seconds,dbo.FNC_RETURN_HOUR_MINUTE_SECONDS(@TotalSecs) as 'Working Days',@retval

This was VERY helpful. Thanks!

Excellent. Thanks very much

Post a Comment