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 ««12345»»»

Calculating the Number of Business Hours Passed Since a Point of Tme Expand / Collapse
Author
Message
Posted Thursday, January 3, 2008 3:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:31 PM
Points: 378, Visits: 2,400
Dear carlosdanielsousa,
Here is the bug:
You need to move the code

if dbo.ufninc_DataAMeiaNoite(@VarCalcCreateDate) in (select dbo.ufninc_DataAMeiaNoite (EffectiveIntervalStart) from dbo.CalendarRuleBase where ExtentCode=2 and SubCode=5)
set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1)


7 lines down its present position in your code, ie just after

set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1)
end


Because of this the value @DataInicio is never getting equal to @DataFim and it is stuck into an infinite loop.

Other than this you seems to have done it impeccably and undrestood the essence. Congrats!

Secondly, in the course of debug I found out another bug which originated from my script.
Replace
if DATEPART(dw, @VarCalcCreateDate) = 1

with
if DATEPART(dw, @VarCalcCreateDate) = 7

(This mistake happened because in my workplace the weekly off days are Thursday and Friday. I translated it for the SQLServerCentral script to make it Saturday, Sunday. The mistake happened in the course of this change.)

Let us know your feedback.

Regards,
Maz


-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
Post #438655
Posted Thursday, January 3, 2008 4:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 8, 2009 5:38 AM
Points: 7, Visits: 43
Hello again, Mazharuddin Ehsan


Ok, that works but it still have a problem if i put the end date in an holiday:

select dbo.ufninc_CalcTimeSinceCreated(convert(datetime,'26-12-2007',105),convert(datetime,'1-1-2008',105))

Best Regards
Post #438677
Posted Friday, January 4, 2008 3:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:31 PM
Points: 378, Visits: 2,400
Hi carlos,
Nice to see you back. I know the reason. This is because you need to modify your function dbo.ufninc_CalcGetDate

I have clarified this in the first posting in this forum. Please check.

Add the below code(with your tablename):

while (1 = 1)
begin
if dbo.DateAtMidnight(@EndDate) in (select dbo.DateAtMidnight(Holiday) from dbo.ListOfHolidays)
set @EndDate = dbo.DateAt730(@EndDate + 1)
else
break
end


in your function dbo.ufninc_CalcGetDate atthe end.

Regards,
Maz


-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
Post #438830
Posted Friday, January 4, 2008 8:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 8, 2009 5:38 AM
Points: 7, Visits: 43
select dbo.CalcTimeBetweenTwoDates(getdate()-1,getdate())

your new code without any changes by me returns 8.5 hours instead of the 8 hours.
Post #438997
Posted Saturday, January 5, 2008 11:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:31 PM
Points: 378, Visits: 2,400
Dear Carlos,
You had a similar remark earlier also

when i execute the function "select dbo.ufninc_CalcTimeSinceCreated (getdate(),getdate()+1)" it return 9 hours instead of the 8 hours.


Then you sloved it yourself.

By the way, it is working correctly for me (see attached).

select dbo.CalcTimeBetweenTwoDates(getdate()-1,getdate())
is resulting 0 for me because it is Saturday today.

So clearly you are missing something.

Regards,
Maz


-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time


  Post Attachments 
CalcTimeBetweenTwoDates.doc (56 views, 94.50 KB)
Post #439268
Posted Thursday, January 17, 2008 10:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:44 AM
Points: 7, Visits: 100
VERY COOL. We needed to be able to calculate time between the creation date of a help ticket and the time it was first modified. Without being able to take in account business hours and holidays any returns we came up with were useless. I just modified the code to reflect our 8-5 business hours, and removed the lunch hour deduction. Again thanks a BUNCH!!
Post #444479
Posted Friday, January 18, 2008 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:44 AM
Points: 7, Visits: 100
[quote]Mazharuddin Ehsan (12/28/2007)
Incidently, I am also using this solution to calculate and report the duration passed for the unresolved IT HelpDesk cases at my work place. The results are displayed on an overhead dashboard for everyone to see.

quote]

How exactly do you pass your variables to the function. We have a date created field and a date modified field. I wish to compare the times between these fields but am having trouble figuring out how to do this. (I am somewhat of a newbie so this probably explains it) It sounds like I am trying to do exactly what you are doing so if you could possibly show me how you have accomplished this it would be greatly appreciated!




Post #445021
Posted Saturday, January 19, 2008 12:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:31 PM
Points: 378, Visits: 2,400
You can use the functions CalcTimeBetweenTwoDates & CalcTimeSinceCreated in any SQL statement, stored procedure or UDF

For example
select dbo.CalcTimeBetweenTwoDates(date_created, date_modified) from [Your Table]

will give the duration between the two fields.

select dbo.CalcTimeSinceCreated(date_created) from [Your Table]

will give you duration passed since the case was created. You can use this function to track the duration since creation for the unresolved cases.

Sincerely,
Maz


-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
Post #445242
Posted Tuesday, January 22, 2008 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:44 AM
Points: 7, Visits: 100
Hmmmm, I must be doing something wrong then, as the code you have described is exactly what I am doing. Initially I was using a view and passing these parameters to the function. Doing this I received this error:

Msg 8115, Level 16, State 2, Procedure CalcTimeBetweenTwoDates, Line 35
Arithmetic overflow error converting expression to data type datetime.


I thought this might be due to the fact that I was using a view so I inserted the values into a table. I then used this code:

select id,TicketCreatedDate,StatusChangeTime
from StatusTimes
where dbo.CalcTimeBetweenTwoDates(TicketCreatedDate,StatusChangeTime) > 4
order by id

but I get the same exact error message.

So I went in and modified my table. I only put one row in the table and everything worked fine. I then put a second row into the table with the EXACT same data. This also worked fine. I then added a row with DIFFERENT data at which time I got the above error.

I have modified your code to remove the lunch hour and I am pretty sure I caused the problem by doing this. I have attached the code for dbo.CalcTimeBetweenTwoDates . I can also add all the other functions if you need me to. Once again your help in this is GREATLY appreciated.


  Post Attachments 
CalcTimeBetweenTwoDates.doc (37 views, 30.00 KB)
Post #445953
Posted Tuesday, January 22, 2008 11:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:31 PM
Points: 378, Visits: 2,400
Oh another bug! Let me catch it this weekend!:)

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
Post #446023
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse