Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function to determine business hours Expand / Collapse
Author
Message
Posted Saturday, October 13, 2007 6:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:32 PM
Points: 180, Visits: 673
Comments posted to this topic are about the item Function to determine business hours
Post #410454
Posted Saturday, January 10, 2009 5:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 11:23 AM
Points: 7, Visits: 147
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
Post #634142
Posted Tuesday, May 04, 2010 2:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 11, 2011 4:47 AM
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.
Post #915639
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse