How to calculate business hours

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

General SQL Server Forums

New to SQL Server Programming

How to calculate business hours

Author

Topic

ATL21002
Starting Member

4 Posts

Posted - 2009-03-12 : 11:55:38

I am a real beginner to SQL Server, and I have this request to calculate business hours counting only weekdays from 8:00am - 6:00pm, excluding weekend and holidays. I've searched and found these scripts posted on this website. I copied these scripts and created table as instructed. However, when tested, the hours did not come out correctly.

@start_date = '6 mar 2009 11:00:00 AM',
@end_date = '7 mar 2009 12:00:00 PM'

with this input, I am expecting a return of 7 hours. However, it return total of 16 hours.

Can someone look at the script and tell me what I missed? I really appreciate your assistance.

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', '8:00:00 AM', '6:00:00 PM', 37800 union all
select 2 'Tuesday', '8:00:00 AM', '6:00:00 PM', 52200 union all
select 3, 'Wednesday','8:00:00 AM', '6:00:00 PM', 52200 union all
select 4, 'Thursday', '8:00:00 AM', '6:00:00 PM', 52200 union all
select 5, 'Friday', '8:00:00 AM', '6:00:00 PM', 52200 union all
select 6, 'Saturday', null, null, 32400 union all
select 7, 'Sunday', null, null, 32400

declare @start_date datetime,
@end_date datetime

select @start_date = '6 mar 2009 11:00:00 AM',
@end_date = '7 mar 2009 12: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

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-12 : 12:30:58

http://www.experts-exchange.com/Software/Office_Productivity/Productivity_Applications/Q_22632288.html

ATL21002
Starting Member

4 Posts

Posted - 2009-03-12 : 14:43:34

I don't have an account with 'experts-exchange.com'. Would you please post the solution here? Thanks.

andrewz00
Starting Member

15 Posts

Posted - 2009-03-12 : 15:11:24

for experts exchange... just scroll allllllllll the way down you can see the solutions. its just a gimmic to get ppl to pay

ATL21002
Starting Member

4 Posts

Posted - 2009-03-12 : 16:04:01

I tried that...and I got a blank screen!

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-13 : 05:28:26

I've never paid to use a site so it must have a free registration.
The page actually contains a link to another site with a formula for working it out:
http://www.kenhamady.com/form13.shtml

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources