SQL Server Forums
Profile | Register | 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
 New Topic  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
17636 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
17636 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
17636 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
17636 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
17636 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
30242 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
17636 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
 New Topic  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.77 seconds. Powered By: Snitz Forums 2000