Post reply

business hours

  • March 27, 2012 at 1:26 am

    #266189

    hi

    i am new to this forum

    my problem is i have table to define the working hours of the employees based on that i need to calculate the hours

    there is no such rule of common shift timeing

    >>Problem statements

    Have Creation date of ticket

    Employee work in diffrent shift based on the World country they support

    so weekoff and shift time are in one table

    and list of holidays in another table

    so now i need to calculate the time spent as of now... received time and current time - i should get the business hours

    Table 1 : Shift start Time | Shift End time | Weekoff1 | weekoff2 |

    Table 2 : Holidays

    Result should be business hours

  • March 27, 2012 at 1:28 am

    #1464747

    take a look at the function DATEDIFF in BOL or Google, it will get the difference between two datetime fields which you can then say is the amount of time between shiftstart and shiftend

  • March 27, 2012 at 1:33 am

    #1464750

    i need a function or sp to define it.. as it happens on a bulk of 4000 records at a time

    common functions

    datediff results in time but not in business hours

  • March 27, 2012 at 1:57 am

    #1464763

    what defines a business hour/working hour?

    how are business hours/working hours calculated?

  • March 28, 2012 at 3:35 am

    #1465557

    We had a similar problem here. My solution was to use datediff to work out the number of minutes remaining in the shift on the first day and the number of minutes from the start of the last day until the problem was fixed and then add on the number of days other than the first and last and multiply it by the number of minutes in a working day.

    So, if the employee got a call with 10 minutes of the day remaining I'd have

    10

    Then they took 50 minutes into the final day to fix it

    10 + 50

    Then there were three days where the call was not completed

    3 X (number of minutes in a day) so for a 7.5 hour day that's 450 minutes a day making a total of 1350

    So the end equation would be 10 + 50 + 1350.

    So may also need to subtract some time if it covers the weekend

Viewing 5 posts - 1 through 4 (of 4 total)

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