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


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


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

Author
Message
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 2445
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

carlosdanielsousa
carlosdanielsousa
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 2445
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

carlosdanielsousa
carlosdanielsousa
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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.
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 2445
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

Attachments
CalcTimeBetweenTwoDates.doc (62 views, 94.00 KB)
PeteO-430407
PeteO-430407
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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!!
PeteO-430407
PeteO-430407
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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:heheSmile 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!
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 2445
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

PeteO-430407
PeteO-430407
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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.
Attachments
CalcTimeBetweenTwoDates.doc (58 views, 30.00 KB)
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 2445
Oh another bug! Let me catch it this weekend!Smile

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

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