## Calculating working hours between 2 dates.

 Author Message be05x5 SSC Rookie Group: General Forum Members Points: 40 Visits: 22 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 BorojevicDate: 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. */ASBEGIN --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 @MinuteDiffEND Nevyn Hall of Fame Group: General Forum Members Points: 3554 Visits: 3149 So you are doing the time in minutes, not hours, correct?Working on a set based example for you ... Luis Cazares SSC-Forever Group: General Forum Members Points: 42396 Visits: 19838 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 CazaresDate: 2014-05-22Purpose: 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 tableAS 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.WorkingHoursFROM Mytable tCROSS APPLY [dbo].[CalcWorkingHours] (t.StartDate, t.EndDate) wh`EDIT: Correction in value for holidays calculation Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 Nevyn Hall of Fame Group: General Forum Members Points: 3554 Visits: 3149 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 thingsCREATE 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 rulesCREATE 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 ENDFROM 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 usedINSERT [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,0UNION ALLSELECT 1, '07:30:00', '09:44:59','Morning pre-break',1,1UNION ALLSELECT 1, '09:45:00', '09:59:59','Morning break',1,0UNION ALLSELECT 1, '10:00:00', '12:29:59','Morning post break',1,1UNION ALLSELECT 1, '12:30:00', '12:59:59','Lunch',1,0UNION ALLSELECT 1, '13:00:00', '15:59:59','Afternoon work',1,1UNION ALLSELECT 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 TABLEAS 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 usesEDIT: Fixed the end time to match the test case. Had 5pm by accident originally. Damn 24 hour clock Luis Cazares SSC-Forever Group: General Forum Members Points: 42396 Visits: 19838 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.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 be05x5 SSC Rookie Group: General Forum Members Points: 40 Visits: 22 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. be05x5 SSC Rookie Group: General Forum Members Points: 40 Visits: 22 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. Nevyn Hall of Fame Group: General Forum Members Points: 3554 Visits: 3149 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. Evil Kraig F SSC-Insane Group: General Forum Members Points: 20905 Visits: 7660 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 FarrellNever 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 NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA ikjeft01 SSC Rookie Group: General Forum Members Points: 34 Visits: 9 not a fan of all those magic numbers.... 960? 465? 450? 585?Also, i'm thinking the nine times "DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)" is called, and the nine times "DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0)" is called surely takes longer than defining a variable for each and computing each once. Your mileage may vary.That... and it doesn't work properly. Try the following:`select * from dbo.calcworkingHours('2014-01-01 06:00','2014-01-01 15:21');`I'm pretty sure the answer shouldn't be negative 1.4 hours.