Calculating the Number of Business Hours Passed Since a Point of Tme

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mazharuddin Ehsan » Calculating the Number of Business Hours...

44 posts, Page 2 of 5

««1 2 3 4 5 »»»

Calculating the Number of Business Hours Passed Since a Point of Tme

Rate Topic

Display Mode

Topic Options

Author

Message

Mazharuddin Ehsan

Posted Thursday, January 03, 2008 3:18 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

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

carlosdanielsousa

Posted Thursday, January 03, 2008 4:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 08, 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

Mazharuddin Ehsan

Posted Friday, January 04, 2008 3:09 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

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

carlosdanielsousa

Posted Friday, January 04, 2008 8:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 08, 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

Mazharuddin Ehsan

Posted Saturday, January 05, 2008 11:41 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

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

PeteO-430407

Posted Thursday, January 17, 2008 10:58 AM

Forum 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

PeteO-430407

Posted Friday, January 18, 2008 10:39 AM

Forum 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 Hehe

) 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

Mazharuddin Ehsan

Posted Saturday, January 19, 2008 12:29 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

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

PeteO-430407

Posted Tuesday, January 22, 2008 9:35 AM

Forum 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 (32 views, 30.00 KB)

Post #445953

Mazharuddin Ehsan

Posted Tuesday, January 22, 2008 11:30 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

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 »

44 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions