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

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    Comments posted to this topic are about the item Calculating The Number Of Business Hours Passed Since a Point of Time [/url]

    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

    MCITP, Developer & Admin

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • carlosdanielsousa

    SSC-Addicted

    Points: 445

    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.

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    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

    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)

    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

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • carlosdanielsousa

    SSC-Addicted

    Points: 445

    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?

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    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.

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • carlosdanielsousa

    SSC-Addicted

    Points: 445

    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 regards

    Carlos Sousa, Portugal

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    Wonderful! Do you mean that you have 1.5 hours of lunch break at your work place. I envy you.

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • carlosdanielsousa

    SSC-Addicted

    Points: 445

    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?

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    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.

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • carlosdanielsousa

    SSC-Addicted

    Points: 445

    DataAMeiaNoite = dateatmidnight

    DataInicioTrab= dateAt730

    DataAlmocoInicio = dateAt1130

    DataAlmocoFim= dateAt12

    DataFIMTrab = dateAt16

    DataInicio= startDate

    DataFim= endDate

    [font="System"]

    ALTER function [dbo].[ufninc_CalcTimeSinceCreated](@DataInicio DateTime,@DataFim DateTime)

    returns real

    as

    begin

    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[/font]

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    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

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • carlosdanielsousa

    SSC-Addicted

    Points: 445

    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

    SSCarpal Tunnel

    Points: 4548

    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

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • carlosdanielsousa

    SSC-Addicted

    Points: 445

    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

    SSCarpal Tunnel

    Points: 4548

    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

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Viewing 15 posts - 1 through 15 (of 46 total)

You must be logged in to reply to this topic. Login to reply