Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating working hours between 2 dates. Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, May 22, 2014 1:46 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, February 18, 2015 11:40 AM Points: 6, Visits: 22
Post #1573766
 Posted Thursday, May 22, 2014 4:23 PM
 SSC Eights! Group: General Forum Members Last Login: Thursday, December 3, 2015 10:32 AM Points: 940, Visits: 3,149
 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
 SSCertifiable Group: General Forum Members Last Login: Today @ 11:54 AM Points: 7,472, Visits: 16,217
 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
Post #1573827
 Posted Thursday, May 22, 2014 5:25 PM This worked for the OP
 SSC Eights! Group: General Forum Members Last Login: Thursday, December 3, 2015 10:32 AM Points: 940, Visits: 3,149
 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 :)
Post #1573836
 Posted Thursday, May 22, 2014 5:34 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 11:54 AM Points: 7,472, Visits: 16,217
 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
Post #1573839
 Posted Wednesday, July 16, 2014 2:21 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, February 18, 2015 11:40 AM Points: 6, 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.
Post #1593298
 Posted Friday, July 18, 2014 8:09 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, February 18, 2015 11:40 AM Points: 6, 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.
Post #1594072
 Posted Friday, July 18, 2014 11:33 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, December 3, 2015 10:32 AM Points: 940, Visits: 3,149
 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 Group: General Forum Members Last Login: Wednesday, September 23, 2015 3:34 PM Points: 5,467, Visits: 7,660
 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
Post #1594264
 Posted Thursday, October 30, 2014 5:37 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, October 30, 2014 5:33 PM Points: 4, 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.
Post #1630015

 Permissions