Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculating working hours between 2 dates. Expand / Collapse
Author
Message
Posted Thursday, May 22, 2014 1:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:08 AM
Points: 6, Visits: 20
I Googled around for a while for a solution on this and not finding one I decided to write/share my own.

Any and all feedback is welcomed. Thanks.

Purpose: This function will return working hours between given 2 dates.
This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM.
This function also assumes that the working hours are between 7:30 AM and 4 PM.

There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.

CREATE function [dbo].[fnc_myHinkley_ASSY_CalcWorkingMinutes] (@StartDate datetime, @EndDate datetime)
RETURNS decimal(14,2)
/*
Programmer: Goran Borojevic
Date: 5/14/2014
Purpose: This function will return working hours between given 2 dates.
This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM.
*/
AS
BEGIN

--check if one of the dates is null
if @StartDate is null or @EndDate is null
RETURN 0

if @EndDate < @StartDate
return 0

declare @StartDateAsDate datetime, @EndDateAsDate datetime
declare @MinuteDiff decimal(14,2)
DECLARE @FullDays int, @Holidays int;

set @StartDateAsDate = convert(datetime, convert(nvarchar(15), @StartDate, 101))
set @EndDateAsDate = convert(datetime, convert(nvarchar(15), @EndDate, 101))


set @MinuteDiff = 0;

--first decide are we in the same day?
IF @StartDateAsDate = @EndDateAsDate
begin
--GB. 5.21.2014 we are in the same day so lets calculate just for the same day difference in minutes
set @MinuteDiff = datediff(MI, @StartDate, @EndDate)

--now if the time falls during break lets substratct 15 minutes
if (@StartDate <= dateadd(mi,9.75*60, @StartDateAsDate) and @EndDate >= dateadd(mi,10*60, @StartDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 15
END

if (@StartDate <= dateadd(mi,12*60, @StartDateAsDate) and @EndDate >= dateadd(mi,12.5*60, @StartDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 30
END

GOTO ReturnDiff
end
ELSE
--so we didnt have the transaction in the same day. Now lets determine in this case.
BEGIN
--first we will take difference from start to the end of the day.
IF @StartDate < DATEADD(MI, 16*60, @StartDateAsDate) --SINCE WE WORK TILL 4 PM.
BEGIN
SET @MinuteDiff = datediff(mi, @StartDate, DATEADD(MI, 16*60, @StartDateAsDate))
END

--GB. 5.21.2014 next lets take away any breaks or lunches out:
--now if the time falls during break lets substract 15 minutes
if (@StartDate <= dateadd(mi,9.75*60, @StartDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 15
END

if (@StartDate <= dateadd(mi,12*60, @StartDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 30
END

--GB. 5.21.2014 next lets get the number of whole days between the 2 specified dates.

set @FullDays = 0

while @StartDateAsDate < @EndDateAsDate-1
BEGIN
if datename(dw,@StartDateAsDate) not in ('Saturday','Sunday')
BEGIN
Set @FullDays = @FullDays + 1;
END
set @StartDateAsDate = @StartDateAsDate +1
END

--important reset the start date
set @StartDateAsDate = convert(datetime, convert(nvarchar(15), @StartDate, 101))

Set @Holidays = isnull((select count(id) from ESYNERGY.dbo.publicholidays where [Date] between @StartDateAsDate and @EndDateAsDate), 0)

set @MinuteDiff = @MinuteDiff + ((@FullDays-@Holidays) * 7.75*60)
--number of full days days [minus] holidays [times] 7 hours and 45 minutes per day [times] 60 minutes

--finally get the last day of the transaction

IF @EndDate > DATEADD(MI, 7*60, @EndDateAsDate) --SINCE WE WORK from 7.30 AM some might come early.
BEGIN
SET @MinuteDiff = @MinuteDiff + datediff(mi, DATEADD(MI, 7*60, @EndDateAsDate), @EndDate)
END

--GB. 5.21.2014 next lets take away any breaks or lunches out:
--now if the time falls during break lets substratct 15 minutes
if (@EndDate >= dateadd(mi,10*60, @EndDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 15
END

if (@EndDate >= dateadd(mi,12.5*60, @EndDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 30
END

END

ReturnDiff:

return @MinuteDiff
END





Post #1573766
Posted Thursday, May 22, 2014 4:23 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 613, Visits: 2,120
So you are doing the time in minutes, not hours, correct?

Working on a set based example for you ...
Post #1573823
Posted Thursday, May 22, 2014 4:31 PM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,374, Visits: 7,303
I might have overcomplicated this, but it might be worth it. Unless someone else comes with a better version of this. If you want to go further, you can generate variables to determine different schedules for working hours, break and lunch.
I didn't test the holiday part because I obviously don't have your table, but it's commented for you to test the functionality.

This is the function I came out with:
DECLARE   @StartDate DATETIME =  '20140523 13:30'
,@EndDate DATETIME= '20140522 16:00:00'
GO
/*
Programmer: Luis Cazares
Date: 2014-05-22
Purpose: This function will return working hours between given 2 dates.
This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM.
This function also assumes that the working hours are between 7:30 AM and 4 PM.
This function was inspired by Goran Borojevic
*/
CREATE function [dbo].[CalcWorkingHours] (@StartDate datetime, @EndDate datetime)
RETURNS table
AS RETURN
SELECT ISNULL((((DATEDIFF(dd,@StartDate,@EndDate)-1) --Start with total number of days including weekends
- (DATEDIFF(wk,@StartDate,@EndDate)*2) --Subtact 2 days for each full weekend
- (1-SIGN(DATEDIFF(dd,6,@StartDate)%7)) --If StartDate is a Sunday, Subtract 1
- (1-SIGN(DATEDIFF(dd,5,@EndDate) %7))) * 465) --This will give us full days minus one that we'll complete with following operations
+ (SELECT CASE WHEN @StartDate <= DATEADD(MI, 450, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 465 --if Start Date is earlier than 7:30 then it counts as full day
WHEN @StartDate >= DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 0 --if Start Date is later than 16:00 then it won't count
ELSE DATEDIFF(MI, @StartDate, DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))) --Minutes between start date and 16:00
-
CASE WHEN @StartDate <= DATEADD(MI, 585, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 15 --If it's earlier than the break, substract whole break
WHEN @StartDate <= DATEADD(MI, 600, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
--If it's earlier than the end of the break, substract corresponding minutes
THEN DATEDIFF( MI, @StartDate, DATEADD(MI, 600, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0)))
ELSE 0
END
-
CASE WHEN @StartDate <= DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 30 --If it's earlier than lunch, substract whole lunch time
WHEN @StartDate <= DATEADD(MI, 780, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
--If it's earlier than the end of lunch time, substract corresponding minutes
THEN DATEDIFF( MI, @StartDate, DATEADD(MI, 780, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0)))
ELSE 0
END
END

+ CASE WHEN @EndDate <= DATEADD(MI, 450, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 0 --if End Date is earlier than 7:30 then it won't count
WHEN @EndDate >= DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 465 --if End Date is later than 16:00 then it counts as full day
ELSE DATEDIFF(MI, DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)), @EndDate) --Minutes between 7:30 and end date
-
CASE WHEN @EndDate >= DATEADD(MI, 600, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 15 --If it's later than the break, substract whole break
WHEN @EndDate >= DATEADD(MI, 585, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
--If it's later than the start of the break, substract corresponding minutes
THEN DATEDIFF( MI, DATEADD(MI, 585, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)), @EndDate)
ELSE 0
END
-
CASE WHEN @EndDate >= DATEADD(MI, 780, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 30 --If it's later than lunch, substract whole lunch time
WHEN @EndDate >= DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
--If it's later than the start of lunch time, substract corresponding minutes
THEN DATEDIFF( MI, DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)), @EndDate)
ELSE 0
END
END
WHERE @StartDate <= @EndDate)
/* Uncomment to use holidays table
- ((SELECT count(id)
FROM ESYNERGY.dbo.publicholidays
WHERE [Date] BETWEEN @StartDateAsDate AND @EndDateAsDate) * 465)
*/
,0) / 60.0 AS WorkingHours

This is an inline table-valued function which should outperform the scalar function that you wrote. But don't trust me, test it yourself and share the results.
You'll have to call the function in a different way, something like this:
SELECT t.StartDate, 
t.EndDate,
wh.WorkingHours
FROM Mytable t
CROSS APPLY [dbo].[CalcWorkingHours] (t.StartDate, t.EndDate) wh

EDIT: Correction in value for holidays calculation



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1573827
Posted Thursday, May 22, 2014 5:25 PM This worked for the OP Answer marked as solution
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 613, Visits: 2,120
I don't know Luis, I think that's not complicated enough :)

A quick example building some tables to work with. It was thrown together quickly so some testing may be in order and the Calendar and TimeOfDay tables are not all they could be as of yet

--First, lets make a Calendar table
-- Note: usually, this would have some more fields so it could be used for other things

CREATE TABLE [dbo].[Calendar](
[calendar_date] [date] NOT NULL,
[month_nbr] [tinyint] NOT NULL,
[month_name] [varchar](10) NOT NULL,
[day_of_week_nbr] [tinyint] NOT NULL,
[day_of_week_name] [varchar](10) NOT NULL,
[year_nbr] [int] NOT NULL,
[IsWeekend] [bit] NOT NULL,
[IsHoliday] [bit] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
[calendar_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Now, a TimeOfDay table (same note as above)
CREATE TABLE [dbo].[TimeOfDay](
[TimeOfDay] [time](0) NOT NULL,
[hour_nbr] [tinyint] NOT NULL,
[minute_nbr] [tinyint] NOT NULL,
CONSTRAINT [PK_TimeOfDay] PRIMARY KEY CLUSTERED
(
[TimeOfDay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Now we create a ScheduleTimes table. If this was a working solution, you would have a parent Schedule table too describing each schedule, but I skipped it
-- this table lets you customize all those business rules
CREATE TABLE [dbo].[ScheduleTimes](
[time_id] [int] IDENTITY(1,1) NOT NULL,
[schedule_id] [int] NOT NULL,
[start_time] [time](0) NOT NULL,
[end_time] [time](0) NOT NULL,
[time_description] [varchar](255) NULL,
[in_working_day] [bit] NOT NULL,
[is_working_time] [bit] NOT NULL,
CONSTRAINT [PK_ScheduleTimes] PRIMARY KEY CLUSTERED
(
[time_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


-- Now lets load the Calendar table with some test dates. For now I only make christmas day a holiday. Make your own holiday rules or even set them as you do with your holiday table

;
WITH t AS (
SELECT 1 as mynum UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1),
quicktally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 rn
FROM t t1, t t2, t t3
),
dates AS (
SELECT DATEADD(day, qt.rn, CAST(getdate() AS DATE)) datetally
FROM quicktally qt
)
INSERT Calendar (calendar_date, month_nbr, month_name, day_of_week_nbr, day_of_week_name, year_nbr, IsWeekend, IsHoliday)
SELECT d.datetally,DATEPART(month,d.datetally),DATENAME(month,d.datetally),DATEPART(weekday,d.datetally),DATENAME(weekday,d.datetally),DATEPART(year,d.datetally),CASE WHEN DATENAME(weekday,d.datetally) IN ('Saturday','Sunday') THEN 1 ELSE 0 END, CASE WHEN MONTH(d.datetally)=12 AND DAY(d.datetally)=25 THEN 1 ELSE 0 END
FROM dates d



-- Now, fill up TimeOfDay

;
WITH t AS (
SELECT 1 as mynum UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1),
quicktally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 rn
FROM t t1, t t2, t t3, t t4
),
times AS (
SELECT CAST (DATEADD(MINUTE, qt.rn, CAST(CAST (getdate() AS DATE) AS DATETIME)) AS TIME(0)) timetally
FROM quicktally qt
)
INSERT TimeOfDay (TimeOfDay, hour_nbr, minute_nbr)
SELECT TOP 1440 ti.timetally, DATEPART(HOUR,ti.timetally),DATEPART(minute,ti.timetally)
FROM times ti


-- And now add times for the schedule your function used

INSERT [dbo].[ScheduleTimes] (schedule_id, start_time, end_time, time_description, in_working_day, is_working_time)
SELECT 1, '00:00:00','07:29:59','Morning pre-work',0,0
UNION ALL
SELECT 1, '07:30:00', '09:44:59','Morning pre-break',1,1
UNION ALL
SELECT 1, '09:45:00', '09:59:59','Morning break',1,0
UNION ALL
SELECT 1, '10:00:00', '12:29:59','Morning post break',1,1
UNION ALL
SELECT 1, '12:30:00', '12:59:59','Lunch',1,0
UNION ALL
SELECT 1, '13:00:00', '15:59:59','Afternoon work',1,1
UNION ALL
SELECT 1, '16:00:00', '23:59:59','Afternoon post work',0,0


-- Now, we define our function. Inline table valued as with Luis'

CREATE FUNCTION dbo.SetBasedWorkingMinutes (@StartDate datetime, @EndDate datetime, @schedule_id int)
RETURNS TABLE
AS
RETURN (
SELECT SUM(CASE WHEN c.IsWeekend = 0 AND c.IsHoliday = 0 AND s.in_working_day = 1 THEN 1 ELSE 0 END) AS WorkingDayMinutes,
SUM(CASE WHEN c.IsWeekend = 0 AND c.IsHoliday = 0 AND s.is_working_time = 1 THEN 1 ELSE 0 END) AS WorkedMinutes,
FROM ( SELECT CAST(c.calendar_date AS datetime) + CAST(t.TimeOfDay AS datetime) DateAndTime,c.calendar_date ,t.TimeOfDay,c.IsWeekend,c.IsHoliday
FROM Calendar c
CROSS JOIN TimeOfDay t
) c
JOIN ScheduleTimes s
ON c.TimeOfDay BETWEEN s.start_time AND s.end_time
WHERE c.DateAndTime >= @StartDate
AND c.DateAndTime < @EndDate
AND s.schedule_id = @schedule_id

)


GO

NOTES:
-The function added a parameter to link to the schedule times table (so you could create a 9-5 schedule, for example, and run the function against it)
-The function is returning minutes, not hours
-Since the function is table valued for performance, I gave you 2 return fields, WorkingDayMinutes counts the minutes from the start to end time without worrying about breaks, WorkedMinutes should match what you had in your query)
- This example took a while to put together even using quickly thrown together data, so it should be tested carefully before being used
- Because it actually selects instead of parsing date/times, the wider your date range, the worse it will perform compared to Luis' . But it should still be pretty fast (2 years took me 1 second) as long as the size of the calendar table is reasonable.
- The ScheduleTimes table lets you customize business rules around breaks and such, and use the same function for different working schedules
- It would actually perform better I think with one date/time calendar table with a record per minute (make better use of an index), but I like the tables separate as both have other uses


EDIT: Fixed the end time to match the test case. Had 5pm by accident originally. Damn 24 hour clock :)
Post #1573836
Posted Thursday, May 22, 2014 5:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,374, Visits: 7,303
Nice Nevyn
To be honest, I'd prefer your option over mine, but I wanted to avoid dependance on the schema in case that someone can't create new tables.
Yours is simple and clean.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1573839
Posted Wednesday, July 16, 2014 2:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:08 AM
Points: 6, Visits: 20
Grrr my email blocker blocked all of my notifications. I didn't see the responses.

Thanks for the responses guys. I shall check it out and write back.
Post #1593298
Posted Friday, July 18, 2014 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:08 AM
Points: 6, Visits: 20
Functions are good unfortunately I only have a SQL 2005 at this time so the Date variable type is not available to me.
Thanks for the responses. Mine works as well.
Post #1594072
Posted Friday, July 18, 2014 11:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 613, Visits: 2,120
The date variable is fairly easy to work around I think. Instead of casting to that type, you cast to a string, substring off the time, cast back to a datetime.
Post #1594174
Posted Friday, July 18, 2014 4:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:13 PM
Points: 6,175, Visits: 7,254
Nevyn (7/18/2014)
The date variable is fairly easy to work around I think. Instead of casting to that type, you cast to a string, substring off the time, cast back to a datetime.


Just for reference:

DATEADD( dd, DATEDIFF( dd, 0, @dt), 0) is faster than CONVERT( DATETIME, CONVERT( VARCHAR(10), @dt, 101)) in the million row test. About twice as fast if memory serves.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1594264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse