SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Function to determine business hours


Function to determine business hours

Author
Message
jdgonzalez@jdandrachel.com
jdgonzalez@jdandrachel.com
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 760
Comments posted to this topic are about the item Function to determine business hours
douglas spencer
douglas spencer
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 159
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
guzun.allex
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
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.
sasken
sasken
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3434 Visits: 1948
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search