0 Replies Latest reply on Oct 31, 2014 1:48 PM by 2649339

I've been looking for the number of business hours between two points.  We designed the workflow with metrics in mind.  So I wanted to see the number of business hours from the time we open the SR to the time we complete the first task.    I was able to find the business days calculation in Mike Larson's book, however I was unsuccessful in fiding a way to break it down to working hours.  I found the thread on time passage calculation, however I couln't get it to work with a time difference between two different points.  I saw the question was posed many times on the boards here, so I thought I would share what I came up with.

(CASE

WHEN DAYOFYEAR("Service Request"."Opened Date and Time")=DAYOFYEAR(Activity."Completed Date")

THEN TIMESTAMPDIFF(SQL_TSI_HOUR,"Service Request"."Opened Date and Time", Activity."Completed Date")

WHEN WEEK("Service Request"."Opened Date and Time")=WEEK(Activity."Completed Date")

THEN TIMESTAMPDIFF(SQL_TSI_HOUR,"Service Request"."Opened Date and Time", Activity."Completed Date") - (15*TIMESTAMPDIFF(SQL_TSI_DAY, "Service Request"."Opened Date and Time", Activity."Completed Date"))

ELSE TIMESTAMPDIFF(SQL_TSI_HOUR,"Service Request"."Opened Date and Time", Activity."Completed Date") - (15*TIMESTAMPDIFF(SQL_TSI_DAY, "Service Request"."Opened Date and Time", Activity."Completed Date"))-(18*TIMESTAMPDIFF(SQL_TSI_WEEK, "Service Request"."Opened Date and Time", Activity."Completed Date"))

END)

1.       If the SR Opened Date and the Activity Completed Date are the same day, then we can just calculate the difference between the two timestamps.

2.       If the SR Opened Date and the Activity Completed Date are on different days, but in the same week, we can calculate the difference between the two time stamps and then subtract 15 hours for each overnight period in between (assuming an 8-5 working day).

3.       If the SR Opened Date and the Activity Completed Date are on different days and in different weeks, we can calculate the difference between the two time stamps and then subtract 18 hours for the weekend

Here are a few examples:

1.       SR Opened On 10/28 at 8:00am and the Task was completed on 10/28 at 10:00am.  We take the timestamp difference in hours – so we have 2 business hours

2.       SR Opened on 10/28 at 8:00am and the Task was completed on 10/29 at 10:00am.

Step 1:  Calculate the timestamp difference in hours.  This is 26 hours.

Step 2:  Calculate the number of days between the two dates.  In this case, we would use the DAYOFYEAR function.  The dayofyear for 10/29 is 302 and the dayofyear for 10/28 is 301.  We now take the    difference between the two dates:  302-301=1.

Step 3:  Multiply 15 hours for the number of days between the two date stamps.  302-301=1.  1*15=15.

Step 4:   Subtract this from the timestamp difference between the opened and completed time.  So 26-15=11 business hours.

This makes sense since we had 9 business hours on day 1 and 2 business hours on day 2.

3.       If the SR Opened on 10/28 8:00am and the Task was completed on 11/4 at 10am.  We follow the same logic as above, then take it one step further.

Step 1: The timestamp difference between the opened and completed date is 170.

Step 2:  The dayofyear for 11/4 is 308 and the dayofyear for 10/28 is 301.  So there are 308-301=7 days between the start and complete dates.

Step 3:  Multiply 15 hours for the number of days between the two date stamps:  7*15=105.

Step 4:  Exclude the weekend hours of 8-5.  To do this we need to know how may weekends have passed.  We can do this by utilizing the week function.  The weekofyear for 11/4 is 45 and the weekofyear for 10/28 is 44.  Since 45-44=1, there is only one weekend in between the start and complete dates.  So we subtract 18 (9 hours for each day – remember we already accounted for the 15 nonworking hours in step 2)

Step 5:  Bring it all together.  170 total hours – 105 nonworking hours – 18 weekend hours = 47 business hours

This makes sense since we had 9 business hours 10/28, 10/29, 10/30, 10/31, 11/3 and 2 working hours on 11/4.

So I can write my case statement like this (simplified for visual aesthetics):

CASE

WHEN DAYOFYEAR=DAYOFYEAR THEN TIMESTAMPDIFF(HOUR,SR Created Time, Activity Completed Time)

WHEN WEEK=WEEK THEN TIMESTAMPDIFF (HOUR, SR Created Time, Activity Completed Time) – (15*TIMESTAMPDIFF(DAY, SR Created, Activity Completed Time))

ELSE TIMESTAMPDIFF(HOUR, SR Created Time, Activity Completed Time) – (15*TIMESTAMPDIFF(DAY, SR Created, Activity Completed time)) – (18*TIMESTAMPDIFF(WEEK, SR Created Time, Activity Completed Time))