Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  How to calculate Business Hours

Author  Topic

latingntlman
Yak Posting Veteran

96 Posts

 Posted - 2006-11-08 : 14:45:07 I have to perform a calculation between two datetimes, but I need to exclude non-business hours and weekends.i.e. DateSentToShipping, DateShippedCan this be done? If so, How?thx,JOhn

Norwich
Posting Yak Master

158 Posts

 Posted - 2006-11-08 : 16:41:18 It can be done. (Almost anything can be done in QA except make coffee).Because you didn't post any info on you problem: I will just give you a framework that you can work from.`Declare @DateShipped datetime, @DateDelivered Datetime, @DayDiff int, @x intSet @DateShipped = '2006/06/17'Set @DateDelivered = '2006/06/24'If Datename(dw,@DateShipped) = 'Saturday' --If Shipped date falls on a Sat then Move the date to the following Date Begin --If it falls on a Sunday then move it to Monday. Set @DateShipped = Dateadd(dd, 2,@DateShipped) --This logic applies to the Delivery date. EndElse If Datename(dw,@DateShipped) = 'Sunday' Begin Set @DateShipped = Dateadd(dd, 1,@DateShipped) EndIf Datename(dw,@DateDelivered) = 'Saturday' Begin Set @DateDelivered = Dateadd(dd, 2,@DateDelivered) EndElse If Datename(dw,@DateDelivered) = 'Sunday' Begin Set @DateDelivered = Dateadd(dd, 1,@DateDelivered) EndSelect @DayDiff = Datediff(d, @DateShipped, @DateDelivered)Print @DayDiff --[Totals Days (incld Wkends)]Select @x = Sum(@DayDiff/7)if @x >= 1 Begin Set @DayDiff = @DayDiff-(@x*2) EndPrint @DayDiff --[Totals Days (excld Wkends)] `I added code that moves the Ship/Delivery date to a Monday if it's on Saturday Or Sunday.Let me know if you need something more specific.RegardsNThe revolution won't be televised!

pir8d
Starting Member

10 Posts

 Posted - 2007-03-04 : 19:15:04 Good Morning All,I'm after a variation to the original post, something a little more detailed.I need to calculate the business hours between two dates. Currently I have the business hours outlined in a table as below:`CREATE TABLE [dbo].[ttr_calendar] ( [day_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL , [day_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [begin_time] [datetime] NULL , [end_time] [datetime] NULL , [duration] [real] NULL ) ON [PRIMARY]`The table looks something like this:`[day_number] [day_name] [begin_time] [end_time] [duration]1 Monday 6:30:00 AM 5:00:00 PM 378002 Tuesday 6:30:00 AM 9:00:00 PM 522003 Wednesday 6:30:00 AM 9:00:00 PM 522004 Thursday 6:30:00 AM 9:00:00 PM 522005 Friday 6:30:00 AM 9:00:00 PM 522006 Saturday 8:00:00 AM 5:00:00 PM 324007 Sunday 8:00:00 AM 5:00:00 PM 32400`Note: The duration column is a calculated using DATEDIFF(ss,begin_time,end_time) and is shown in seconds for accuracy.Now, given a start_date and an end_date, does anyone have any ideas how I can calculate the business hours between those dates?Eg. start_date = '15 Feb 2007 11:00:00 AM'end_date = '17 Feb 2007 10:00:00 PM'+ 10 hours (15 Feb 11:00AM - 15 Feb 9:00PM business hours)- 9.5 hours (15 Feb 9:00PM - 16 Feb 6:30AM non-business hours)+ 14.5 hours (16 Feb 6:30AM - 16 Feb 9:00PM business hours)- 9.5 hours (16 Feb 9:00PM - 17 Feb 6:30AM non-business hours)+ 14.5 hours (17 Feb 6:30AM - 17 Feb 9:00PM business hours)- 1 hour (17 Feb 9:00PM - 17 Feb 10:00PM non-business hours)= 39 hours

khtan
In (Som, Ni, Yak)

17689 Posts

 Posted - 2007-03-04 : 20:28:19 [code]drop table #ttr_calendarCREATE TABLE [dbo].[#ttr_calendar] ( [day_number] [varchar] (50) NOT NULL , [day_name] [varchar] (50) NULL , [begin_time] [datetime] NULL , [end_time] [datetime] NULL , [duration] [real] NULL ) ON [PRIMARY]insert into #ttr_calendarselect 1, 'Monday', '6:30:00 AM', '5:00:00 PM', 37800 union allselect 2, 'Tuesday', '6:30:00 AM', '9:00:00 PM', 52200 union allselect 3, 'Wednesday', '6:30:00 AM', '9:00:00 PM', 52200 union allselect 4, 'Thursday', '6:30:00 AM', '9:00:00 PM', 52200 union allselect 5, 'Friday', '6:30:00 AM', '9:00:00 PM', 52200 union allselect 6, 'Saturday', '8:00:00 AM', '5:00:00 PM', 32400 union allselect 7, 'Sunday', '8:00:00 AM', '5:00:00 PM', 32400declare @start_date datetime, @end_date datetimeselect @start_date = '15 Feb 2007 11:00:00 AM', @end_date = '17 Feb 2007 10:00:00 PM'select total_hours = sum(case when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then case when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time) else duration end when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then case when @end_date < [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date) else duration end else duration end ) / 60.0 / 60.0from F_TABLE_DATE(@start_date, @end_date) d inner join #ttr_calendar c on d.WEEKDAY_NAME_LONG = c.day_name[/code]using F_TABLE_DATE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATEKH

pir8d
Starting Member

10 Posts

 Posted - 2007-03-04 : 21:21:53 Hi KH,Thanks for the info and the quick reply, the function and the script you wrote work nicely, when calculating the total_hours, it seems to just take the sum of the days in which is spans across. Perhaps we need to add/subtract the difference between the start_date and the begin_time for that day and add/subtract the difference between the end_date and the end_time for that day?Eg. If we use:select @start_date = '15 Feb 2007 11:00:00 AM', @end_date = '15 Feb 2007 3:00:00 PM'We get 14.5 hours, which is the total number of hours for that business day, but the actual business hours between the start_date and end_date is 4 hours.Sorry, I know this query is getting quite complicated, your assistance is very much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

 Posted - 2007-03-04 : 21:26:20 missed out the condition when start_date and end_date are same day.`drop table #ttr_calendarCREATE TABLE [dbo].[#ttr_calendar] ( [day_number] [varchar] (50) NOT NULL , [day_name] [varchar] (50) NULL , [begin_time] [datetime] NULL , [end_time] [datetime] NULL , [duration] [real] NULL ) ON [PRIMARY]insert into #ttr_calendarselect 1, 'Monday', '6:30:00 AM', '5:00:00 PM', 37800 union allselect 2, 'Tuesday', '6:30:00 AM', '9:00:00 PM', 52200 union allselect 3, 'Wednesday', '6:30:00 AM', '9:00:00 PM', 52200 union allselect 4, 'Thursday', '6:30:00 AM', '9:00:00 PM', 52200 union allselect 5, 'Friday', '6:30:00 AM', '9:00:00 PM', 52200 union allselect 6, 'Saturday', '8:00:00 AM', '5:00:00 PM', 32400 union allselect 7, 'Sunday', '8:00:00 AM', '5:00:00 PM', 32400declare @start_date datetime, @end_date datetimeselect @start_date = '15 Feb 2007 11:00:00 AM', @end_date = '15 Feb 2007 03:00:00 PM'select total_hours = sum(case when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then datediff(second, @start_date, @end_date) when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then case when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time) else duration end when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then case when @end_date < [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date) else duration end else duration end ) / 60.0 / 60.0from F_TABLE_DATE(@start_date, @end_date) d inner join #ttr_calendar c on d.WEEKDAY_NAME_LONG = c.day_name`KH

khtan
In (Som, Ni, Yak)

17689 Posts

 Posted - 2007-03-04 : 21:27:19 by the way, the nice function F_TABLE_DATE() is written by MVJ.KH

pir8d
Starting Member

10 Posts

 Posted - 2007-03-04 : 21:36:55 Wow, thanks again for the quick reply. That solution that you have there is exactly what I was looking for, it is incredible.Thanks also to MVJ for the awesome F_TABLE_DATE function.

pir8d
Starting Member

10 Posts

 Posted - 2007-03-12 : 20:31:24 Hi khtan,Thanks again for your advice on this code. Just after one more bit of advice.I have got the above code to work correctly in a stored procedure - it takes two parameters (start_date and end_date) and it returns an output (total_hours).If I have a table of values, eg.`CREATE TABLE [dbo].[#ttr_calendar] ( [row_id] [varchar] (50) NOT NULL , [start_date] [datetime] NULL , [end_date] [datetime] NULL , [total_hours] [real] NULL ) ON [PRIMARY]`What I would like to do is run through the table and perform the stored procedure on each row, and store the total_hours returned from the stored procedure in the total_hours row.Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

 Posted - 2007-03-12 : 21:55:13 your start_date and end_date should be of same date day right ? then just use datediff(hour, start_date, end_date)KH

pir8d
Starting Member

10 Posts

 Posted - 2007-03-12 : 22:16:27 Hi khtan,But if I just run DATEDIFF then it won't take into account the business hours calculations you helped me create earlier on in this thread. Sorry if I wasn't clear enough, I would like to populate the total_hours field with the result of the stored procedure (which is a calculation of the total business hours between the two dates).Cheers.

khtan
In (Som, Ni, Yak)

17689 Posts

 Posted - 2007-03-12 : 22:34:59 create a function out of the script`create function fn_workinghour( @start_date datetime, @end_date datetime)returns decimal(10,2)asbegin declare @total_hours decimal(10,2) select @total_hours = sum(case when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then datediff(second, @start_date, @end_date) when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then case when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time) else duration end when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then case when @end_date < [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date) else duration end else duration end ) / 60.0 / 60.0 from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c on d.WEEKDAY_NAME_LONG = c.day_name return @total_hoursend`Then use it to calculate the total hours`update tset total_hours = dbo.fn_workinghour(start_date, end_date)from yournewtable t`KH

pir8d
Starting Member

10 Posts

 Posted - 2007-03-12 : 22:57:15 Thanks again mate, exactly what I was looking for. I tried doing that with a function but had problems as it doesn't allow the use of temp tables. Changed them to actual tables like your code above and it works exactly as I wanted.Cheers!

MKD
Starting Member

2 Posts

 Posted - 2007-04-10 : 09:33:26 Wow.Its very helpful.Can yoiu tell me how can I ignore the weekends please?Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2007-04-10 : 09:44:23 Ignore dates that has weekdayname Sunday and Saturday.Peter LarssonHelsingborg, Sweden

MKD
Starting Member

2 Posts

 Posted - 2007-04-18 : 16:44:00 I want to ignore weekends and Take time only from 2pm to 5 pm on Friday and 10 am to 3 pm on Monday.It is not showing hrs correctly.(For example My starttime is Friday 2Pm and end time is 3PM Monday.I am expecting answer as 2 hrs.1 hr on Friday and 1 hr on Monday.I change minutes values in the table calendar.Sti;ll no luck.Any ideasDeclare @start_date datetimeDeclare @end_date datetimedeclare @total_hours decimal(10,2)set @start_date='4/13/2007 8:00:00 AM'set @end_date='4/17/2007 5:00:00 AM'select @total_hours =sum(case when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) and [WEEKDAY_NAME] not in ('Sat','Sun')then datediff(second, @start_date, @end_date) when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) and [WEEKDAY_NAME] not in ('Sat','Sun')then case when @start_date > [DATE] + begin_time and [WEEKDAY_NAME] not in ('Sat','Sun') then datediff(second, @start_date, [DATE] + end_time) else datediff(second, @start_date, [DATE] + end_time) end when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then case when @end_date < [DATE] + end_time and [WEEKDAY_NAME] not in ('Sat','Sun') then datediff(second, [DATE] + begin_time, @end_date) else datediff(second, [DATE] + begin_time, @end_date) end else datediff(second, [DATE] + begin_time, @end_date) end ) / 60.0 / 60.0 from F_TABLE_DATE(@start_date, @end_date) d inner join calendar c on d.WEEKDAY_NAME_LONG = c.day_namePrint @total_hours

thecrow55
Starting Member

1 Post

 Posted - 2007-08-08 : 06:21:18 I know this Thread is a bit old... but an additional tip to the well presented sample would be appreciate it.When the start_date and end_date are on the same day, you are using just datediff no matter if the start or end dates are outside business hours.Could it be possible this sample: the start_date is at 6am but business hours start at 8am. We are at 10am. So the result should be 2 hours and not 4.Thanks in advance for your help!

FSA
Starting Member

4 Posts

 Posted - 2007-10-01 : 17:20:56 I know the thread is dated but is it possible to subtract holidays from the start and end dates. Can someone show me how to add that to the query?KHTAN I really appreciated the solution you posted!Thanks.

FSA
Starting Member

4 Posts

 Posted - 2007-10-01 : 19:37:25 I need to clarify my request. In addition to calculating business hours, I also need to subtract and holiday hours. The holidays are maintained in a table.Again, I appreciate any help!

khtan
In (Som, Ni, Yak)

17689 Posts

 Posted - 2007-10-01 : 21:39:31 do you have a holiday table ? `from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c on d.WEEKDAY_NAME_LONG = c.day_name left join holiday h on d.DATE = h.DATEwhere h.DATE is NULL`KH[spoiler]Time is always against us[/spoiler]

FSA
Starting Member

4 Posts

 Posted - 2007-10-02 : 11:08:39 Hi KHTAN!I was hoping you'd read my request. I implemented your solution and it works. However, when I use the code in a function and do the following:SELECT dbo.fn_workinghour('2007-09-21 23:56:48.000','2007-09-23 23:49:44.000')I get -5.95.Should I input the dates differently?My code is like this:SELECT dbo.fn_Workinghour(OPEN_TIME, CLOSE_TIME)FROM MyActivityTableBoth OPEN_TIME and CLOSE_TIME are DATETIME fields.Thank you for your help!