Solved

# Function to calculate number of worked hours between two datetime values

Posted on 2013-01-07
Medium Priority
3,273 Views
I need to write a function to calculate the number of worked hours between two datetime fields assuming the person's normal work hours are 9 to 5 and they don't work weekends. Has anyone written such a function in SQL before please?
0
Question by:Grffster
• 8
• 5
• 3
• +4

LVL 49

Expert Comment

ID: 38751310
Does this mean that you want to ignore hours prior to 9AM or after 5PM on weekdays, and ignore any hours recorded on weekends?

So if an individual worked 08:00 - 16:00, you would only count 09:00-16:00?
0

Author Comment

ID: 38751316
Yes, that's right.
0

LVL 8

Expert Comment

ID: 38751320
When dealing with day-of-week calculations, it's important to take account of the current DATEFIRST settings. This query will always correctly exclude weekend days, using @@DATEFIRST to account for any possible setting for the first day of the week.

``````SELECT *
FROM your_table
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)
``````

http://stackoverflow.com/questions/1803987/how-do-i-exclude-weekend-days-in-a-sql-server-query

Then you need to divide for hours number.
0

LVL 15

Expert Comment

ID: 38751329
You don't need a function for this

SELECT SUM(DATEDIFF(hh,FromDate, ToDate))
FROM TableName
WHERE DATENAME(WEEKDAY,FromDate) NOT IN ('Saturday','Sunday')
AND  DATENAME(WEEKDAY,ToDate) NOT IN ('Saturday','Sunday')
0

LVL 8

Expert Comment

ID: 38751339
You can get the difference between the two dates to whatever resolution you want (in your example, minutes):

``````DATEDIFF(minute, @start_date, @end_date)
``````
0

Author Comment

ID: 38751418
I need to exclude any time after 5pm and before 8am each day though.
0

LVL 70

Expert Comment

ID: 38751672
Not many details, but my best guess is you're working with one day's start and end times.

I'll assume 8-5 rather than 9-5: adjust if it really is 9-5.

And, for now, I'll assume a work shift doesn't cross days; let me know if it can.

SELECT
CASE WHEN DATEPART(HOUR, work_end) < 8 OR DATEPART(HOUR, work_start) >= 17 THEN 0
ELSE DATEDIFF(MINUTE,
CASE WHEN DATEPART(HOUR, work_start) < 8 THEN '08:00' ELSE CONVERT(char(5), work_start, 8) END,
CASE WHEN DATEPART(HOUR, work_end) > 17 THEN '17:00' ELSE CONVERT(char(5), work_end, 8) END)
END AS minutes_worked
FROM dbo.tablename
0

Author Comment

ID: 38753929
It will be across days, so an example would be with a standard work day of 9am-5pm and no work on weekends, if i wanted to work out the number of minutes worked between 14:30 on a friday and 10:45 on the following monday the number of minutes will be 255 minutes (i.e. 150 minutes between 14:30 and 17:00 on the friday, and 105 minutes for 09:00-10:45 on the monday morning.
0

LVL 28

Expert Comment

ID: 38765247
I did something like this recently, and just posted a script:

http://trycatchfinally.net/2013/01/calculating-working-hours-between-two-dates/

To count the number of hours between two dates, you have to use a list of all the hours and select the ones that meet your criteria - you can do this by using the RANK() OVER() function (assuming SQL 2005+). Once you have that, you flag certain hours as working (in this case, M-F and 8AM-5PM), and then join and count the rows.

You'd set it up like this:

``````-- Set things up before we get started
--------------------------------------
DECLARE @WorkTimeStart      TINYINT,
@WorkTimeEnd        TINYINT,
@WorkDayOfWeekStart TINYINT,
@WorkDayOfWeekEnd   TINYINT

DECLARE @StartDate          DATETIME,
@EndDate            DATETIME

CREATE TABLE #WorkingHours (
EvaluateTime    DATETIME,
IsWorktime      BIT DEFAULT(0)
)

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

SET @WorkTimeStart = 7  --7AM
SET @WorkTimeEnd   = 16 --4PM hour (4-5PM count as working)
SET @WorkDayOfWeekStart = 2 --Monday
SET @WorkDayOfWeekEnd   = 6 --Friday

SET @StartDate  = '2000-01-01 00:00:00'
SET @EndDate    = '2020-12-31 23:59:59'

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

-- Built the list of timestamps we're working with
;WITH numberlist(number)
AS (SELECT RANK() over(order by c1.object_id,
c1.column_id,
c2.object_id,
c2.column_id)
from sys.columns c1
cross
join sys.columns c2)
INSERT INTO #WorkingHours (EvaluateTime)
FROM numberlist
WHERE DATEADD(hh, number-1, @StartDate) <= @EndDate

-- Set the times to worktime if they match criteria
UPDATE #WorkingHours
SET IsWorktime = CASE WHEN (DATEPART(dw, EvaluateTime)
BETWEEN @WorkDayOfWeekStart
AND @WorkDayOfWeekEnd)
AND
(DATEPART(hh, EvaluateTime)
BETWEEN @WorkTimeStart
AND @WorkTimeEnd) THEN 1
ELSE 0
END
``````

And then once your set up, you can join to your table and count the working hours like this:

``````SELECT EmployeeID,
StartTime,
EndTime,
SUM(  CONVERT(INT, wh.WorkTime)) as WorkHoursBetweenDates
FROM TimeLogs t
JOIN #WorkingHours wh
ON wh.EvaluateTime BETWEEN t.StartTime
AND t.EndTime
GROUP BY EmployeeID,
StartTime,
EndTime
``````

Let me know if you have any questions.
0

LVL 70

Expert Comment

ID: 38765422
Currently the code handles up to 10 days -- increase the "days" CTE if more days are needed.  A CROSS JOIN added there would quickly allow up to 100 days.

``````CREATE FUNCTION dbo.CalcWorkMinutes (
@start_date datetime,
@end_date datetime
)
--SELECT dbo.CalcWorkMinutes('20130104 14:30', '20130107 10:45')
RETURNS int
AS
BEGIN
DECLARE @minutes_worked int
;WITH times AS (
SELECT
DATEDIFF(DAY, @start_date, @end_date) AS #days,
DATEADD(DAY, -DATEDIFF(DAY, 0, @start_date), @start_date) AS start_time,
DATEADD(DAY, -DATEDIFF(DAY, 0, @end_date), @end_date) AS end_time
),
days AS (
SELECT 0 AS day# UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
)
SELECT @minutes_worked = SUM(minutes_worked)
FROM (
-- starting day
SELECT
CASE
WHEN DATEDIFF(DAY, 0, @start_date) % 7 IN (5, 6) OR start_time > '17:00' THEN 0 --Sat/Sun/After-hours
WHEN #days = 0 AND end_time < '09:00' THEN 0 --Before-hours
ELSE DATEDIFF(MINUTE, CASE WHEN start_time < '09:00' THEN '09:00' ELSE start_time END,
CASE WHEN #days > 0 OR end_time > '17:00' THEN '17:00' ELSE end_time END)
END AS minutes_worked
FROM times
UNION ALL
-- inbetween days
SELECT
CASE
WHEN DATEDIFF(DAY, 0, DATEADD(DAY, day#, @start_date)) % 7 IN (5, 6) THEN 0 --Sat/Sun
ELSE 8 * 60
END AS minutes_worked
FROM times
CROSS JOIN days
WHERE
day# BETWEEN 1 AND (#days - 1)
UNION ALL
-- ending day
SELECT
CASE
WHEN DATEDIFF(DAY, 0, @start_date) % 7 IN (5, 6) OR end_time < '09:00' THEN 0 --Sat/Sun/Before-hours
WHEN #days = 0 AND start_time > '17:00' THEN 0 --After-hours
ELSE DATEDIFF(MINUTE, '09:00', CASE WHEN end_time > '17:00' THEN '17:00' ELSE end_time END)
END AS minutes_worked
FROM times
) AS derived
RETURN @minutes_worked
END --FUNCTION
``````
0

LVL 51

Expert Comment

ID: 38765563

There seems to be some good answers above, but I am not sure of the datasource.

Understand the requirement, and no real problems, it can be done with respect to the requested date +time ranges.

But the approach will differ slightly depending on that datasource. If for example there is a "timecard" where people clock in and clock out, then the working day could be represented over a few different rows based on "type" of time record (e.g. "start shift", "start break", "start lunch", "end break", "end shift"). Or, if like a timesheet then likely to have "started" and "ended" times on the timesheet. Or, if like a jobsheet, then might have start + end for multiple jobs within the one day.

So, maybe if you could dummy up a few rows and put in a couple of tricky ones (if there is such a thing), I am sure we can give you a definitive solution.
0

Author Comment

ID: 38766893
Here's some data for you to try out. I'm ignoring break times, so it's only for start and end time and it needs to calculate the number of minutes or hours between two dates excluding the non-working times being before 9am and after 5pm on weekdays, and all weekend.

``````create table #tempvals
(
personid int,
starttime datetime,
endtime datetime
)

insert into #tempvals
select 1, '03 jan 2013 08:00', '03 jan 2013 08:45'
union all
select 2, '03 jan 2013 08:00', '03 jan 2013 09:45'
union all
select 3, '03 jan 2013 11:00', '03 jan 2013 11:45'
union all
select 4, '03 jan 2013 11:00', '03 jan 2013 16:45'
union all
select 5, '03 jan 2013 11:00', '03 jan 2013 17:45'
union all
select 6, '03 jan 2013 11:00', '04 jan 2013 11:00'
union all
select 7, '03 jan 2013 13:00', '07 jan 2013 09:45'
union all
select 8, '05 jan 2013 10:00', '06 jan 2013 09:45'
union all
select 9, '03 jan 2013 16:00', '14 jan 2013 09:45'

select * from #tempvals
``````

I've also attached a spreadsheet highlighting what I'd expect the results to be.
minutes-worked-example.xlsx
0

LVL 70

Expert Comment

ID: 38767790
CORRECTION:

Need to add a WHERE condition to the "last day", then it works fine:

``````ALTER FUNCTION dbo.CalcWorkMinutes (
@start_date datetime,
@end_date datetime
)
--SELECT dbo.CalcWorkMinutes('20130104 14:30', '20130107 10:45')
RETURNS int
AS
BEGIN
DECLARE @minutes_worked int
;WITH times AS (
SELECT
DATEDIFF(DAY, @start_date, @end_date) AS #days,
DATEADD(DAY, -DATEDIFF(DAY, 0, @start_date), @start_date) AS start_time,
DATEADD(DAY, -DATEDIFF(DAY, 0, @end_date), @end_date) AS end_time
),
days AS (
SELECT 0 AS day# UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
)
SELECT @minutes_worked = SUM(minutes_worked)
FROM (
-- starting day
SELECT
CASE
WHEN DATEDIFF(DAY, 0, @start_date) % 7 IN (5, 6) OR start_time > '17:00' THEN 0 --Sat/Sun/After-hours
WHEN #days = 0 AND end_time < '09:00' THEN 0 --Before-hours
ELSE DATEDIFF(MINUTE, CASE WHEN start_time < '09:00' THEN '09:00' ELSE start_time END,
CASE WHEN #days > 0 OR end_time > '17:00' THEN '17:00' ELSE end_time END)
END AS minutes_worked
FROM times
UNION ALL
-- inbetween days
SELECT
CASE
WHEN DATEDIFF(DAY, 0, DATEADD(DAY, day#, @start_date)) % 7 IN (5, 6) THEN 0 --Sat/Sun
ELSE 8 * 60
END AS minutes_worked
FROM times
CROSS JOIN days
WHERE
day# BETWEEN 1 AND (#days - 1)
UNION ALL
-- ending day
SELECT
CASE
WHEN DATEDIFF(DAY, 0, @start_date) % 7 IN (5, 6) OR end_time < '09:00' THEN 0 --Sat/Sun/Before-hours
WHEN #days = 0 AND start_time > '17:00' THEN 0 --After-hours
ELSE DATEDIFF(MINUTE, '09:00', CASE WHEN end_time > '17:00' THEN '17:00' ELSE end_time END)
END AS minutes_worked
FROM times
[b]	WHERE  --add bolded lines (rest of func is the same)
#days > 0[/b]
) AS derived
RETURN @minutes_worked
END --FUNCTION
``````

SELECT *, dbo.CalcWorkMinutes(starttime, endtime)
FROM (
SELECT 1 AS personid, '1/3/2013 8:00' AS starttime, '1/3/2013 8:45' AS endtime, 0 AS expected_minutes UNION ALL
SELECT 2, '1/3/2013 8:00', '1/3/2013 9:45', 45 UNION ALL
SELECT 3,      '1/3/2013 11:00',      '1/3/2013 11:45',      45 UNION ALL
SELECT 4,      '1/3/2013 11:00',      '1/3/2013 16:45',      345 UNION ALL
SELECT 5,      '1/3/2013 11:00',      '1/3/2013 17:45',      360 UNION ALL
SELECT 6,      '1/3/2013 11:00',      '1/4/2013 11:00',      480 UNION ALL
SELECT 7,      '1/3/2013 13:00',      '1/7/2013 9:45',      765 UNION ALL
SELECT 8,      '1/5/2013 10:00',      '1/6/2013 9:45',      0 UNION ALL
SELECT 9,      '1/3/2013 16:00',      '1/14/2013 9:45',      2985
) AS test_data
0

LVL 51

Expert Comment

ID: 38769918
Apologies for not replying sooner - currently "fixing" various email issues and Win7 synch...

While acknowledging that scott has written a function as requested... there is no real need, it can be done via a straight SQL query.

``````;with cte_dates as
( select  personid, starttime, endtime
else starttime
end as calc_starttime
else endtime
end as calc_endtime
,datename(dw,starttime) as dow
from    #tempvals
--where   starttime >= @starttime
--and     endtime <= @endtime

union all
select  personid,starttime, endtime
,case when startdate+1 = enddate then endtime
end
,datename(dw,calc_starttime+1), startdate + 1, enddate
from   cte_dates
where  startdate + 1 <= enddate
)
select personid, starttime, endtime, sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end) as minutes
from cte_dates D
group by personid, starttime, endtime
``````

and using the above as a basis, then if really needing a function,

``````CREATE FUNCTION udf_work_minutes (@starttime datetime, @endtime datetime)
RETURNS INT
as
BEGIN

declare @minutes int

;with cte_dates as
( select  starttime, endtime
else starttime
end as calc_starttime
else endtime
end as calc_endtime
,datename(dw,starttime) as dow
from    (select @starttime as starttime, @endtime as endtime) a
union all
select  starttime, endtime
,case when startdate+1 = enddate then endtime
end
,datename(dw,calc_starttime+1), startdate + 1, enddate
from   cte_dates
where  startdate + 1 <= enddate
)
select @minutes = sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end)
from cte_dates D

RETURN (@minutes)

END

GO

-- then to use it against the #tempvals sample

SELECT *, dbo.udf_work_minutes(starttime, endtime) as minutes
FROM #tempvals t
``````

then we can also do an inline TVF which is always considered to perform considerably better than a scaler function.

``````create FUNCTION udf_work_minutes_TVF (@starttime datetime, @endtime datetime)
returns table
as
return (
with cte_dates as
( select  starttime, endtime
else starttime
end as calc_starttime
else endtime
end as calc_endtime
,datename(dw,starttime) as dow
from    (select @starttime as starttime, @endtime as endtime) a

union all

select  starttime, endtime
,case when startdate+1 = enddate then endtime
end
,datename(dw,calc_starttime+1), startdate + 1, enddate
from   cte_dates
where  startdate + 1 <= enddate
)
select sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end) as minutes
from cte_dates D
)
GO

-- and again, to use it

SELECT *
FROM #tempvals t
CROSS APPLY dbo.udf_work_minutes_TVF(t.starttime,t.endtime)
``````

So, there it is...

In these cases, it can often be better / fast if you have a calendar table to join from rather than use an implied or dynamic virtual calendar

have written about virtual calendars which you might be interested in : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1221-Fun-with-MS-SQL-spt-values-for-delimited-strings-and-virtual-calendars.html
0

Author Comment

ID: 38793393
I get a maximum recursion error when I apply the function in myreport. Not sure how to fix it to be honest.
0

LVL 51

Expert Comment

ID: 38794400
OK, well first start with a relatively small set of data to make sure it is all OK.

Make sure you have included whatever "where" clauses to limit the number of rows in the first query.

Then, you can use the MAXRECURSION query hint when you do your final select from the CTE

Can you tell us which one is causing problems for you ?

e.g.

Select *
from cte_dates
option (maxrecursion 0)
0

Author Comment

ID: 38800264
Yes, it all looked fine apart from times running up to 17:00 which I changed in the code from >= to > which seems to have fixed that problem. However, when I run the function against my proper data i.e. from a live table, I get the recursion problem.

I've tried putting the option (maxrecursion 0) in your function at the end like so:

``````select sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end) as minutes
from cte_dates D option (maxrecursion 0)
``````

but it errors with the following description:

``````Incorrect syntax near the keyword 'option'
``````
0

LVL 51

Expert Comment

ID: 38800817
It is normally :

select *
from cte_Dates D
option (maxrecursion 0)

But by the looks of the select command, you dont need that table alias. Which one are you using ?
0

Author Comment

ID: 38801285
Sorry, it's in your table valued function version.

``````    select sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end) as minutes
from cte_dates D option (maxrecursion 0)
``````
0

LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 38814163
Yeah, for whatever reason, it wont happen in the TVF as part of a RETURN() statement. The OPTION has to be supported by the actual statement being used. Works fine in the scaler version (because it is wrapped in a begin / end and used to set the variable being returned = multi-step with the CTE being a seperate statements)

But, you can specify it as part of calling the function itself e.g.

select *
from #tempvals t
cross apply dbo.udf_work_minutes_TVF(t.starttime,t.endtime) option (maxrecursion 0)

It seems a bit stoopid, and while I dont know the official MS reasons... would hazard a guess that one reason could be because the TVF is supposed to be a table I guess and the function is the actual interpretive query, so the statement is not the return() but the select. The same happens for views as well.
0

Author Closing Comment

ID: 38879804
Used the scalar function in the end.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month9 days, 13 hours left to enroll