Calculate Business Hours - SQL Server Forums

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 2008 Forums

Other SQL Server 2008 Topics

Calculate Business Hours

Author

Topic

nas79
Starting Member

2 Posts

Posted - 2010-07-29 : 23:08:32

Hi,

I am new to this forum and i need some help.

I am trying to calculate business hours between two days excluding week-end and holidays. I created a function that will help me do the calculation. The issue i am currently facing is the business hours start at 7:00 AM to 8:00PM. all calculation works perfectly except. when calculating business hours for one day example

let stay i want to calculate business hours between '2010-07-06 01:15:19.903' and '2010-07-06 7:25:43.287' my function return 6.17 hrs which should of returned 0.43hrs. If i change the start date to the 5th it does return 0.43 hrs which is corrected as the 5th of July was a Holiday.

Please see below my function and advise :

USE [Reports]
GO
/****** Object: UserDefinedFunction [rpt].[fnCalcBusinessHours] Script Date: 07/29/2010 23:06:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Function [rpt].[fnCalcBusinessHours] (@dtStart datetime,@dtEnd datetime)

RETURNS DECIMAL (7,2)

BEGIN
DECLARE
@HoursFDay decimal (7,2),
@HoursLDay decimal (7,2),
@Days decimal (7,2),
@Hours decimal (7,2),
@dtDayBegin datetime

BEGIN

SET @HoursFDay = 0
SET @HoursLDay = 0

END

BEGIN
IF rpt.fnDateValue(@dtStart) = rpt.fnDateValue(@dtEnd)
BEGIN
SET @hours = DATEDIFF(SECOND,@dtStart,@dtEnd)/3600
RETURN @Hours
END

ELSE BEGIN
IF DATEPART(WEEKDAY,@dtStart) in (7,1) OR EXISTS(SELECT * FROM rpt.tblBusinessDays
WHERE bBusinessDay = 0
AND dtDate = CAST(@dtStart AS date))
BEGIN
SET @HoursFday = 0
SET @dtStart = (SELECT MIN(dtDate)
FROM rpt.tblBusinessDays
WHERE bBusinessDay = 1
AND dtDate > @dtStart)
END

ELSE BEGIN
SET @HoursFDay = rpt.fnBusinessHoursToEndOfDay(@dtStart)
END

IF DATEPART(WEEKDAY,@dtEnd) in (7,1) OR EXISTS(SELECT * FROM rpt.tblBusinessDays
WHERE bBusinessDay = 0
AND dtDate = CAST(@dtEnd AS Date))
BEGIN
SET @HoursLday = 0
SET @dtEnd = (SELECT MIN(dtDate)
FROM rpt.tblBusinessDays
WHERE bBusinessDay = 1
AND dtDate > @dtEnd)
END

ELSE BEGIN
SET @HoursLDay = rpt.fnBusinessHoursFromStartOfDay(@dtEnd)
END

-- Set for beginning times if we dont' have times then the same day will count as 1 day and not 0
SET @dtStart = CAST(@dtStart + ' 7:00:00 AM' AS DATETIME)
SET @dtEnd = CAST(@dtEnd + ' 8:00:00 PM' AS DATETIME)

--- subtract 1 day because we only need whole business day between two days
SET @Days = (SELECT distinct COUNT(dtDate)-1
FROM rpt.tblBusinessDays
WHERE dtDate BETWEEN @dtstart AND @dtEnd
AND bBusinessDay = 1)
SET @Days = CASE WHEN @Days < 0 THEN 0 ELSE @Days END
SET @Hours = (@Days * 13) + @HoursFDay + @HoursLDay
END
END

RETURN @Hours
END

nas79
Starting Member

2 Posts

Posted - 2010-08-14 : 16:22:06

Any help??

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-15 : 02:24:51

what is the issue here? if i undestand you correctly you need to have your function taking into consideration also holidays? in this case you need to feed your function list of holidays date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-15 : 03:38:33

see

http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources