Calculation for Elapsed SLA Time

[donofdons89]

Can anyone help with the following problem I am trying to over come?

I have a database where as a support desk log all support calls. Very simple Start Date Start Time (creation time and Date) we also have SLA times in this database set in hours. Our service department is open Monday to Friday from 9am until 5pm.

I would like a field that shows me the total number of hours and minutes a call has been logged for minus non working hours. I have adapted a calculation I have found on the web but it seems to be ignoring non working hours... Can anyone please help? I would be very grateful

(Int ((call_date - call_date) / 7) * 5 + Mod (Get(CurrentDate) -call_date; 7) - If (Mod (Get(CurrentDate) - Date (1; 3; 1904); 7) < Mod (Get(CurrentDate) - Date (1; 3; 1904); 7); 2; 0)) * 28800 + Get(CurrentTime)- call_time

[comment]

the total number of hours and minutes a call has been logged for minus non working hours.

What exactly does that mean? If a call started at 4:30pm and ended at 5:15pm - does that count as 30 minutes?

Is it possible for a call to cross midnight?

we also have SLA times in this database set in hours.

And what exactly does that mean?

[donofdons89]

Sorry, in answer to question 1 yes that is correct, and then if the call passed over to the following day and was closed at 09:30 that would be an hour.

As for question 2 SLA would an amount of hours we need to complete the call in. e.g SLA of 8 hours. No to worry to much on this as it is for our reference only. I can do conditional formatting based on these times. e.g if the result of a call is 9 hours and the SLA is 8 I can show that we missed our quota by 1 hour

[donofdons89]

Has anyone any further thoughts on this?

Thanks

[bcooney]

Automatic message

This topic has been moved from "FileMaker Classifieds → Solutions Wanted" to "Database Schema & Business Logic → Calculation Engine (Define Fields)".

[donofdons89]

Is this possible ? I have it working for week days but it is including weekends.

[comment]

Yes, it is possible. No, it is not simple.

I would start by calculating how many workdays have elapsed - see:

http://fmforums.com/forum/topic/42681-subtracting-dates/

Multiply this by 8 hours and subtract the "missing" workhours of the starting and ending days.

[Cabinetman]

I may be way off base here but if I get the reasoning for the final outcome ... why not a new table that holds each call ?

IncidentNumber

CallNumber (unique)

StartTime

EndTime

Duration (calcualtion)

DurationSummary

Then relate to the original table by the IncidentNumber .... add a field there that is a summary total of all calls with matching IncidentNumber. Even set up a portal so you can see all the calls for this IncidentNumber.

[comment]

IIUC, this is not about duration of calls as such, but rather the time elapsed between reporting an issue and its resolution (or current time, if the issue has not been yet resolved).

[Cabinetman]

My bad. I understood it slightly different.

As :

I charged $100 to help you fix this problem.

Based upon the problem I expected it to take 1 hr of tech time.

My tech spent 30 min.'s today and 45 yesterday

Tech spent 15 min.'s more than expected.....

As opposed to:

SLA is to fix within 8 hrs .... excluding after hours?

Service call came in Friday at 2:00 PM.

We spoke until 3:00 PM (1 hour)

We spoke again 9:00 AM Monday until 10:00 AM (1 hour)

SLA would be 5 hours ? ( Approx.)

Actual call time 2 hours.

Oh well......

[comment]

I am only guessing too, but I think it's something like:

Call came in Friday at 2:00 PM; it's now Tuesday 11:00 AM.

Days to count: Friday, Monday, Tuesday = 3 * 8 = 24 hours

Subtract 5 hours for Friday and 6 hours for Tuesday = 24 - 11 = 13 workhours since opening the issue.

Contract says issues need to be resolved in 8 workhours, therefore this issue is 5 hours overdue (and counting...).

[donofdons89]

I may be way off base here but if I get the reasoning for the final outcome ... why not a new table that holds each call ?

IncidentNumber

CallNumber (unique)

StartTime

EndTime

Duration (calcualtion)

DurationSummary

Then relate to the original table by the IncidentNumber .... add a field there that is a summary total of all calls with matching IncidentNumber. Even set up a portal so you can see all the calls for this IncidentNumber.

OK but how do I exlcude the non working hours from the summary?

Our working hours are 9am until 5pm If I open an IncidentNumber on Friday at 4PM then close that IncidentNumber on the following Monday at 10am the call elapsed time would be a total of 2hours.

My bad. I understood it slightly different.

As :

I charged $100 to help you fix this problem.

Based upon the problem I expected it to take 1 hr of tech time.

My tech spent 30 min.'s today and 45 yesterday

Tech spent 15 min.'s more than expected.....

As opposed to:

SLA is to fix within 8 hrs .... excluding after hours?

Service call came in Friday at 2:00 PM.

We spoke until 3:00 PM (1 hour)

We spoke again 9:00 AM Monday until 10:00 AM (1 hour)

SLA would be 5 hours ? ( Approx.)

Actual call time 2 hours.

Oh well......

OK but how do I exlcude the non working hours from the summary?

Our working hours are 9am until 5pm If I open an IncidentNumber on Friday at 4PM then close that IncidentNumber on the following Monday at 10am the call elapsed time would be a total of 2hours.

I am only guessing too, but I think it's something like:

Call came in Friday at 2:00 PM; it's now Tuesday 11:00 AM.

Days to count: Friday, Monday, Tuesday = 3 * 8 = 24 hours

Subtract 5 hours for Friday and 6 hours for Tuesday = 24 - 11 = 13 workhours since opening the issue.

Contract says issues need to be resolved in 8 workhours, therefore this issue is 5 hours overdue (and counting...).

You are exactly right thank you, any ideas?

[comment]

I am not sure where your difficulty lies. Subtract 9 hours from the time the incident was opened; subtract the time the incident was closed from 17 hours, and sum the two results.

Using your last example:

16 - 9 = 7;

17 - 10 = 7;

7 + 7 = 14;

Two workdays = 2 * 8 hours = 16 hours;

16 - 14 = 2

---

This is assuming incidents are opened and closed during working hours - otherwise the calculations must be adjusted.

Edited October 31, 2011 by comment

[donofdons89]

I am not sure where your difficulty lies. Subtract 9 hours from the time the incident was opened; subtract the time the incident was closed from 17 hours, and sum the two results.

Using your last example:

16 - 9 = 7;

17 - 10 = 7;

7 + 7 = 14;

Two workdays = 2 * 8 hours = 16 hours;

16 - 14 = 2

---

This is assuming incidents are opened and closed during working hours - otherwise the calculations must be adjusted.

Sorry to be a little thick but this can be done in one calculation field?

[comment]

Sure, why not?

[donofdons89]

Sure, why not?

Ok I have just tried that solution and it works fine if the call is taken in given example but as soon as I have a call opened and closed the same day e.g opened 21/10/11 @ 15:00 and closed on 21/10/11 @16:00 I get the returned result of 30:59:34 ??

Have I done something incorrect?

[comment]

Have I done something incorrect?

Yes, obviously. It looks like you are mixing your units: if you want get the result as Time. make sure you express 8 hours as 8*3600 and 9:00 AM as Time ( 9 ; 0 ; 0 ) etc.

[Cabinetman]

You are exactly right thank you, any ideas?

Wow ... gotta be a first for me. Seems I never am right. Go me!

OK but how do I exlcude the non working hours from the summary?

Our working hours are 9am until 5pm If I open an IncidentNumber on Friday at 4PM then close that IncidentNumber on the following Monday at 10am the call elapsed time would be a total of 2hours.

So sorry I haven't chimed in on this. May not be important any more BUT.

In my thinking you are never subtracting any hours. Only adding....

Basic Example:

Table::SupportDeskMainLog

IncidentNumber (unique)

Client

Whatever1

Whatever2

Table::supportDeskCalls

IncidentNumber

CallNumber (unique)

StartTime

EndTime

Duration (calcualtion)

DurationSummary

You relate them by field IncidentNumber.

First call:

Fri. 2:00 PM

New record in Table::SupportDeskMainLog to create the incident first followed by new record in Table::supportDeskCalls.

Disconnected at 4:00 PM

Call is timed and results in a Duration of 2 hrs.

Second call:

Mon. 9:00 AM

Customer gives IncidentNumber

Create new record in Table::supportDeskCalls with the IncidentNumber but a new CallNumber (unique).

Disconnected at 10:00 AM

Call is timed and results in a Duration of 1 hr.

Put a portal in Table::SupportDeskMainLog

Portal shows 2 calls. One of 2 hrs. and one of 1 hr

At the bottom of portal summary shows 3 hrs on this incident.....

VERY basic file attached....Logs_Test.zip