Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating the Number of Business Hours Passed Since a Point of Tme Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, November 28, 2007 1:17 AM
 Old Hand Group: General Forum Members Last Login: Friday, September 9, 2016 2:50 PM Points: 379, Visits: 2,436
Comments posted to this topic are about the item Calculating The Number Of Business Hours Passed Since a Point of Time

Friends,
As the saying goes ‘Time is money’. Here is a way to compute this precious commodity.
The motivation to write this came from a friend at my workplace who was in dire need of this solution to use in his reporting tool. Then I saw this posting on the SQLserverCentral
Want to Calculate Business Working hours
and decided to write for the community.

As you see, the whole logic is built upon DATEDIFF and DATEADD functions.

The comments of some of the readers motivated me to do enhancements in the solution:
1. To calculate duration between two values of time.
2. Considering the holidays.

See the attachment for the details.

Sincererly,
Mazharuddin

-----------------------------------------------------------
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
 CalculatingDurationOfOfficeHoursPassedBetweenTwoPointsOfTime.doc (724 views, 51.50 KB)
Post #426732
 Posted Thursday, December 27, 2007 4:40 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, May 8, 2009 5:38 AM Points: 7, Visits: 43
 Your work is working perfect on a calculation of SLA for my service desk.I would like to know if it is possible to include holidays e the calculation.
Post #436607
 Posted Friday, December 28, 2007 5:26 AM
 Old Hand Group: General Forum Members Last Login: Friday, September 9, 2016 2:50 PM Points: 379, Visits: 2,436
 Incidently, I am also using this solutuion 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.It can be used for other scenarios which requiere to calculate the time consumed in completing tasks.As I mentioned in the documentation[/quote]The code can be modified to suit any other working hours and days pattern. I used this code to calculate the actual number of working hours passed since a job was started. It can be modified to calculate the number of working hours taken to complete tasks (The function CalcTimeSinceCreated will have to be modified to take two parameters in that case as 'task beginning date&time' and 'task ending date&time', instead of one at present)[quote]To integrate holidays in this solution you need to modify something as follows:1. Store all the holidays in a table Holidays (which has a field DateOff)2. Modify the Function 8 [CalcTimeSinceCreated] as follows: while (1 = 1) begin if dbo.DateAtMidnight(@VarCalcCreateDate) in (select dbo.DateAtMidnight(DateOff) from Holidays)set @VarCalcCreateDate = dbo.DateAt730(@VarCalcCreateDate + 1)I hope you manage to do the enhancement.Regards,Maz -----------------------------------------------------------Time Is MoneyCalculating the Number of Business Hours Passed since a Point of TimeCalculating the Number of Business Hours Passed Between Two Points of Time
Post #437000
 Posted Friday, December 28, 2007 7:54 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, May 8, 2009 5:38 AM Points: 7, Visits: 43
 Hello again, tnx for the answer!I've now another problem, since i have to recalcute all the history of my service desk to show in a report, i've to change your code to accept the start and the end date.I've change your code to accept two parameters, start date and End Date, but when i change the getdate() to enddate parameter, when i execute the function "select dbo.ufninc_CalcTimeSinceCreated (getdate(),getdate()+1)" it return 9 hours instead of the 8 hours. Do you know why?
Post #437056
 Posted Friday, December 28, 2007 9:07 AM
 Old Hand Group: General Forum Members Last Login: Friday, September 9, 2016 2:50 PM Points: 379, Visits: 2,436
 Clearly it is a logical error. At least your code is free from any syntax error. Check carefully. My original program deducts 0.5 hours for the lunch period each day. Possibly the error is around this point. -----------------------------------------------------------Time Is MoneyCalculating the Number of Business Hours Passed since a Point of TimeCalculating the Number of Business Hours Passed Between Two Points of Time
Post #437095
 Posted Friday, December 28, 2007 9:26 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, May 8, 2009 5:38 AM Points: 7, Visits: 43
 you where right! tnx for the answer and for the short waiting time!I changed 1800s to 5400s that is my lunch time, and the code work just like it should!best regardsCarlos Sousa, Portugal
Post #437106
 Posted Saturday, December 29, 2007 8:07 AM
 Old Hand Group: General Forum Members Last Login: Friday, September 9, 2016 2:50 PM Points: 379, Visits: 2,436
 Wonderful! Do you mean that you have 1.5 hours of lunch break at your work place. I envy you. -----------------------------------------------------------Time Is MoneyCalculating the Number of Business Hours Passed since a Point of TimeCalculating the Number of Business Hours Passed Between Two Points of Time
Post #437326
 Posted Thursday, January 3, 2008 4:15 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, May 8, 2009 5:38 AM Points: 7, Visits: 43
 it's good to have such a big lunch time!I'm having another problem, when i make select dbo.CalcTimeSinceCreated(convert(datetime,'31-12-2007',105),convert(datetime,'02-01-2008',105))And i've an holiday in my database for the date (01-01-2008),it goes to a infinite cicle.but when i make select dbo.CalcTimeSinceCreated(convert(datetime,'31-12-2007',105),convert(datetime,'03-01-2008',105))all works fine! do you know why?
Post #438311
 Posted Thursday, January 3, 2008 5:01 AM
 Old Hand Group: General Forum Members Last Login: Friday, September 9, 2016 2:50 PM Points: 379, Visits: 2,436
 I think I can resolve the issue if I see your modified code. Apparently it is not able to get out of the loop if the previous day of the second parameter is a holiday. You need to kill this bug. -----------------------------------------------------------Time Is MoneyCalculating the Number of Business Hours Passed since a Point of TimeCalculating the Number of Business Hours Passed Between Two Points of Time
Post #438324
 Posted Thursday, January 3, 2008 6:21 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, May 8, 2009 5:38 AM Points: 7, Visits: 43
 DataAMeiaNoite = dateatmidnightDataInicioTrab= dateAt730DataAlmocoInicio = dateAt1130DataAlmocoFim= dateAt12DataFIMTrab = dateAt16DataInicio= startDateDataFim= endDateALTER function [dbo].[ufninc_CalcTimeSinceCreated](@DataInicio DateTime,@DataFim DateTime)returns realasbegin declare @CalcHours real declare @VarCalcCreateDate datetime if dbo.ufninc_DataAMeiaNoite(dbo.ufninc_CalcCreateDate(@DataInicio)) = dbo.ufninc_DataAMeiaNoite(dbo.ufninc_CalcGetDate(@DataFim)) set @CalcHours = case when dbo.ufninc_CalcCreateDate(@DataInicio) = dbo.ufninc_DataAlmocoInicio(dbo.ufninc_CalcGetDate(@DataFim)) then round(((cast(datediff(ss, dbo.ufninc_CalcCreateDate(@DataInicio), dbo.ufninc_CalcGetDate(@DataFim)) as real)-5400)/3600),2) else round((cast(datediff(ss, dbo.ufninc_CalcCreateDate(@DataInicio), dbo.ufninc_CalcGetDate(@DataFim )) as real)/3600),2) end else begin set @CalcHours = case when dbo.ufninc_CalcCreateDate(@DataInicio) <= dbo.ufninc_DataAlmocoInicio(dbo.ufninc_CalcCreateDate(@DataInicio)) then round(((cast(datediff(ss, dbo.ufninc_CalcCreateDate(@DataInicio), dbo.ufninc_DataFimTrab(dbo.ufninc_CalcCreateDate(@DataInicio))) as real)-5400)/3600),2) else round((cast(datediff(ss, dbo.ufninc_CalcCreateDate(@DataInicio), dbo.ufninc_DataFimTrab(dbo.ufninc_CalcCreateDate(@DataInicio))) as real)/3600),2) end set @VarCalcCreateDate = dbo.ufninc_CalcCreateDate(@DataInicio) while (1 = 1) begin 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) if DATEPART(dw, @VarCalcCreateDate) = 6 set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 3) else begin if DATEPART(dw, @VarCalcCreateDate) = 1 set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 2) else set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1) end if dbo.ufninc_DataAMeiaNoite(@VarCalcCreateDate) = dbo.ufninc_DataAMeiaNoite(dbo.ufninc_CalcGetDate(@DataFim )) set @CalcHours = @CalcHours + (case when @VarCalcCreateDate = dbo.ufninc_DataAlmocoInicio(dbo.ufninc_CalcGetDate(@DataFim )) then round(((cast(datediff(ss, @VarCalcCreateDate, dbo.ufninc_CalcGetDate(@DataFim )) as real)-5400)/3600),2) else round((cast(datediff(ss, @VarCalcCreateDate, dbo.ufninc_CalcGetDate(@DataFim)) as real)/3600),2) end) if dbo.ufninc_DataAMeiaNoite(@VarCalcCreateDate) = dbo.ufninc_DataAMeiaNoite(dbo.ufninc_CalcGetDate(@DataFim)) BREAK else set @CalcHours = @CalcHours + 8 end end return round(@CalcHours,2) end
Post #438346

 Permissions