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, DateShipped

Can 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 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

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 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)

17689 Posts

Posted - 2007-03-04 : 20:28:19
[code]
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
[/code]

using F_TABLE_DATE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE


KH

Go to Top of Page

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

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_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)

17689 Posts

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


KH

Go to Top of Page

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

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

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

Go to Top of Page

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

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)
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

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

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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

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

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

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.DATE
where h.DATE is NULL



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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 MyActivityTable

Both OPEN_TIME and CLOSE_TIME are DATETIME fields.

Thank you for your help!
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -