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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ
 All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  How to calculate Business Hours Reply to Topic  Printer Friendly
Author  Topic
Page: of 2

latingntlman
Yak Posting Veteran

USA
96 Posts

 Posted - 11/08/2006 :  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

Kenya
158 Posts

 Posted - 11/08/2006 :  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 int Set @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. End Else If Datename(dw,@DateShipped) = 'Sunday' Begin Set @DateShipped = Dateadd(dd, 1,@DateShipped) End If Datename(dw,@DateDelivered) = 'Saturday' Begin Set @DateDelivered = Dateadd(dd, 2,@DateDelivered) End Else If Datename(dw,@DateDelivered) = 'Sunday' Begin Set @DateDelivered = Dateadd(dd, 1,@DateDelivered) End Select @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) End Print @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

Australia
10 Posts

 Posted - 03/04/2007 :  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 37800 2 Tuesday 6:30:00 AM 9:00:00 PM 52200 3 Wednesday 6:30:00 AM 9:00:00 PM 52200 4 Thursday 6:30:00 AM 9:00:00 PM 52200 5 Friday 6:30:00 AM 9:00:00 PM 52200 6 Saturday 8:00:00 AM 5:00:00 PM 32400 7 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)

Singapore
17689 Posts

 Posted - 03/04/2007 :  20:28:19 ``` drop table #ttr_calendar CREATE 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_calendar select 1, 'Monday', '6:30:00 AM', '5:00:00 PM', 37800 union all select 2, 'Tuesday', '6:30:00 AM', '9:00:00 PM', 52200 union all select 3, 'Wednesday', '6:30:00 AM', '9:00:00 PM', 52200 union all select 4, 'Thursday', '6:30:00 AM', '9:00:00 PM', 52200 union all select 5, 'Friday', '6:30:00 AM', '9:00:00 PM', 52200 union all select 6, 'Saturday', '8:00:00 AM', '5:00:00 PM', 32400 union all select 7, 'Sunday', '8:00:00 AM', '5:00:00 PM', 32400 declare @start_date datetime, @end_date datetime select @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.0 from F_TABLE_DATE(@start_date, @end_date) d inner join #ttr_calendar c on d.WEEKDAY_NAME_LONG = c.day_name ```using F_TABLE_DATE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATEKH Edited by - khtan on 03/04/2007 20:38:39

pir8d
Starting Member

Australia
10 Posts

 Posted - 03/04/2007 :  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)

Singapore
17689 Posts

 Posted - 03/04/2007 :  21:26:20 missed out the condition when start_date and end_date are same day.``` drop table #ttr_calendar CREATE 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_calendar select 1, 'Monday', '6:30:00 AM', '5:00:00 PM', 37800 union all select 2, 'Tuesday', '6:30:00 AM', '9:00:00 PM', 52200 union all select 3, 'Wednesday', '6:30:00 AM', '9:00:00 PM', 52200 union all select 4, 'Thursday', '6:30:00 AM', '9:00:00 PM', 52200 union all select 5, 'Friday', '6:30:00 AM', '9:00:00 PM', 52200 union all select 6, 'Saturday', '8:00:00 AM', '5:00:00 PM', 32400 union all select 7, 'Sunday', '8:00:00 AM', '5:00:00 PM', 32400 declare @start_date datetime, @end_date datetime select @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.0 from 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)

Singapore
17689 Posts

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

pir8d
Starting Member

Australia
10 Posts

 Posted - 03/04/2007 :  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

Australia
10 Posts

 Posted - 03/12/2007 :  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)

Singapore
17689 Posts

 Posted - 03/12/2007 :  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 Edited by - khtan on 03/12/2007 22:27:10

pir8d
Starting Member

Australia
10 Posts

 Posted - 03/12/2007 :  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)

Singapore
17689 Posts

 Posted - 03/12/2007 :  22:34:59 create a function out of the script``` create function fn_workinghour ( @start_date datetime, @end_date datetime ) returns decimal(10,2) as begin 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_hours end ```Then use it to calculate the total hours``` update t set total_hours = dbo.fn_workinghour(start_date, end_date) from yournewtable t ```KH

pir8d
Starting Member

Australia
10 Posts

 Posted - 03/12/2007 :  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 - 04/10/2007 :  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

Sweden
30421 Posts

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

MKD
Starting Member

2 Posts

 Posted - 04/18/2007 :  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 Posts

 Posted - 08/08/2007 :  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

USA
4 Posts

 Posted - 10/01/2007 :  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

USA
4 Posts

 Posted - 10/01/2007 :  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)

Singapore
17689 Posts

 Posted - 10/01/2007 :  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.DATE where h.DATE is NULL```KHTime is always against us

FSA
Starting Member

USA
4 Posts

 Posted - 10/02/2007 :  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!
Page: of 2  Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC