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
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to calculate Business Hours
 Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

latingntlman
Yak Posting Veteran

USA
96 Posts

Posted - 11/08/2006 :  14:45:07  Show Profile  Reply with Quote
I have to perform a calculation between two datetimes, but I need to exclude non-business hours and weekends.

i.e. DateSentToShipping, DateShipped

Can this be done? If so, How?

thx,

JOhn

Norwich
Posting Yak Master

Kenya
158 Posts

Posted - 11/08/2006 :  16:41:18  Show Profile  Reply with Quote
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.

Regards
N

The revolution won't be televised!
Go to Top of Page

pir8d
Starting Member

Australia
10 Posts

Posted - 03/04/2007 :  19:15:04  Show Profile  Reply with Quote

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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/04/2007 :  20:28:19  Show Profile  Reply with Quote

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_DATE


KH


Edited by - khtan on 03/04/2007 20:38:39
Go to Top of Page

pir8d
Starting Member

Australia
10 Posts

Posted - 03/04/2007 :  21:21:53  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/04/2007 :  21:26:20  Show Profile  Reply with Quote
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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/04/2007 :  21:27:19  Show Profile  Reply with Quote
by the way, the nice function F_TABLE_DATE() is written by MVJ.


KH

Go to Top of Page

pir8d
Starting Member

Australia
10 Posts

Posted - 03/04/2007 :  21:36:55  Show Profile  Reply with Quote
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.
Go to Top of Page

pir8d
Starting Member

Australia
10 Posts

Posted - 03/12/2007 :  20:31:24  Show Profile  Reply with Quote
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?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/12/2007 :  21:55:13  Show Profile  Reply with Quote
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
Go to Top of Page

pir8d
Starting Member

Australia
10 Posts

Posted - 03/12/2007 :  22:16:27  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/12/2007 :  22:34:59  Show Profile  Reply with Quote
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

Go to Top of Page

pir8d
Starting Member

Australia
10 Posts

Posted - 03/12/2007 :  22:57:15  Show Profile  Reply with Quote
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!
Go to Top of Page

MKD
Starting Member

2 Posts

Posted - 04/10/2007 :  09:33:26  Show Profile  Reply with Quote
Wow.Its very helpful.Can yoiu tell me how can I ignore the weekends please?Thanks a lot
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 04/10/2007 :  09:44:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Ignore dates that has weekdayname Sunday and Saturday.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

MKD
Starting Member

2 Posts

Posted - 04/18/2007 :  16:44:00  Show Profile  Reply with Quote
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 ideas

Declare @start_date datetime
Declare @end_date datetime
declare @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_name
Print @total_hours
Go to Top of Page

thecrow55
Starting Member

1 Posts

Posted - 08/08/2007 :  06:21:18  Show Profile  Reply with Quote

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!
Go to Top of Page

FSA
Starting Member

USA
4 Posts

Posted - 10/01/2007 :  17:20:56  Show Profile  Click to see FSA's MSN Messenger address  Reply with Quote
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.
Go to Top of Page

FSA
Starting Member

USA
4 Posts

Posted - 10/01/2007 :  19:37:25  Show Profile  Click to see FSA's MSN Messenger address  Reply with Quote
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!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 10/01/2007 :  21:39:31  Show Profile  Reply with Quote
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



KH
Time is always against us

Go to Top of Page

FSA
Starting Member

USA
4 Posts

Posted - 10/02/2007 :  11:08:39  Show Profile  Click to see FSA's MSN Messenger address  Reply with Quote
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 MyActivityTable

Both OPEN_TIME and CLOSE_TIME are DATETIME fields.

Thank you for your help!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.38 seconds. Powered By: Snitz Forums 2000