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.
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') "
ReplyDeletewhere there's only one holiday and that is 'saturday ' and business hour from 9am to 1pm .thank u
ReplyDeleteRoji,
ReplyDeleteFirst 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.
This comment has been removed by the author.
ReplyDeleteMr.Plame,
ReplyDeleteThank 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,
ReplyDeleteTo 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
ReplyDeleteHow will you adjust this code if the dates include time portion (so you need to correctly calculate time for the non complete dates)?
ReplyDeleteDo you have any thought about how to include civic holidays to this logic as well? thx!
ReplyDeleteReza,
ReplyDeleteThe 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
ReplyDelete@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!
ReplyDeleteExcellent. Thanks very much
ReplyDeleteselect dbo.CalcBusinessHours('2014-05-14 15:46:16.000','2014-05-14 19:21:41.000')
ReplyDeleteis returning 8 and not 4 why?
Better three hours too soon than a minute too late. See the link below for more info.
ReplyDelete#hours
www.ufgop.org
Hello everyone,
ReplyDeleteI am struggling with the following problem. I need to calculate hours between 2 dates. For example let say that the start day is 01.03.2018 -Thursday and the end day is 06.03.2018 - Tuesday. I want to be able to calculate only the working hours between these 2 days. Let say that working hours are between 9 and 18. I tried a few things but no luck. Any help will be much appreciated. Thanks