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.

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

 

*/

12 comments:

roji said...

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') "

roji said...

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

Plamen Ratchev said...

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.

roji said...
This comment has been removed by the author.
roji said...

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

Plamen Ratchev said...

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

*/

TotallySQL said...

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

Naomi said...

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

Reza said...

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

Plamen Ratchev said...

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.

Izhar Ab Rani said...

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

Anonymous said...

This was VERY helpful. Thanks!