Function to determine business hours

Question

Function to determine business hours

jdgonzalez@jdandrachel.com

Hall of Fame

Points: 3161
More actions
October 13, 2007 at 6:10 pm

#176027

Comments posted to this topic are about the item Function to determine business hours

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

douglas spencer SSC Enthusiast Points: 111 More actions · Answer 1

There are several bugs to this formula when dealing with minutes and the @enddate is after the business hours. Also, there is an issue with identical minutes and the calucation of hours. The @Cnt needs to be a 1 in the event the difference between the @startdate and @enddate is less than 1 hour.

These changes should be incorporated into the code:

select @Cnt=case when datediff(mi,@StartDate,@endDate)%60=0 then 0 else 1 end

select @strip = case when datepart(hh,@enddate) in (0,1,2,3,4,5,6,7,12,17,18,19,20,21,22,23)

then (60-datepart(mi,@startdate))*.0167 else convert(decimal(9,2),right(@hoursBetween,3)) end

that should get you there!

doug

guzun.allex Grasshopper Points: 13 More actions · Answer 2

I have noticed the same bugs and fixed them in the following way:

first I adjust StartDate And EndDate if they are not whithin bussines hours

/*If the start date is after the business hours we will remove the minutes from the StartDate*/

select @StartDate = case when datepart(hh,@StartDate) in (0,1,2,3,4,5,6,7,12,17,18,19,20,21,22,23)

then dateadd(mi,-datepart(mi,@StartDate),@StartDate) else @StartDate end

/*If the End date is after the business hours we will remove the minutes from the EndDate*/

select @endDate = case when datepart(hh,@endDate) in (0,1,2,3,4,5,6,7,12,17,18,19,20,21,22,23)

then dateadd(mi,-datepart(mi,@endDate),@endDate) else @endDate end

then I have changed a bit the while statement

while @Cnt < FLOOR(@HoursBetween)

And it works perfectly, thx.

Pyman SSChampion Points: 13830 More actions · Answer 3

WHat should be modified here if starttime is 9:00AM and endtime is 6:00PM and lunch is 12:00PM to 1:00PM

I will be passing the starttime and endtime as ('2015-01-01 10:20:01.001','2015-01-01 17:29:21.001')

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams