Service Deadline Calculation (1 Viewer)

Hi All,

I'm in the throws of looking at working out live response deadline times (SLA's) on field service calls logged on our database system.

I have already calculated a deadline based on the call type, and the response times and taking this forward wanted to try and include methods to "start" and "stop" the calls deadline clock from ticking.

As an example let say we have a call logged at 09:45 on a Monday. The conditions at the outset of the call are;
1. The call has an 8 hour response agreement.
2. The Service hours agreement for this call are response only between 09:00- 17:00 basis.
The initial deadline set would be for 09:45 on the Tuesday. (8 Hours within the working times already agreed.)
I can already calculate the correct initial deadline for the call.
The difficulty i'm having is working out how to add time to this deadline, based on the calls status changing, e.g. we may be waiting for a response from the customer before we can proceed.
If we put the call to a waiting response how do I "stop the clock" and then ensure it starts again once the status changes.

The calls status is a separate table that is added to with the CallHeaderID and StatusID with a time-date stamp.

My initial thought is to add a field to my call status list that flags if the call timer should be paused, restarted or left alone, my blind spot is how do I adjust the deadline time.
If its paused how do I extend it? At the moment the only thing I can think of is a back ground process that every 5 minutes or so looks at all the paused deadlines and adds 5 minutes to them. It would also be a little challenging to program as we have different response times for different calls (e.g. some are 9-5, some are 8:00-8:00, some are 24/7)
This seems a quite high overhead if I'm missing a trick somewhere.

Has anyone any experience of this type of scenario?

First off a time like this you cant adjust, or shouldnt adjust. Adjusting it suggests you stored it in your table... this is a calculated field... calculated fields are not stored.

Ideally you want to calculate this on the fly when you need it.
Is this entire database access only? or is it partially stored in i.e. Oracle or SQL server?

If it is within access only
Is there some (easy) way to get at the previous status, like having a Key for the status as well as a PreviousStatus field referencing the key again?

I think you only need to work with the CallStatus table.

CallStatusId, CallHeaderID, StatusID, StatusTime
1, 1, 1, 8/15/2015 9:45 AM
2, 1, 2, 8/15/2015 10:45 AM
3, 1, 1, 8/15/2015 11:45 AM
4, 1, 2, 8/15/2015 1:30 PM
5, 1, 1, 8/16/2015 9:00 AM


Here's my assumptions:

CallStatusID is primary key
CallHeaderID is foreign key to main call table - Above data is working with just 1 call
StatusID is foreign key to status table which tells you if status is to run the clock or pause clock - 1=run clock, 2= pause clock
StatusTime is time that status for that call started.

Now to determine the deadline end you create a query to SUM all the total time the call was paused. You said you already can calculate the initial deadline end--hopefully that's a function twith a signature that looks like this:

Function get_WorkDeadline(in_StartTime AS Date, in_DeadlineMinutes As Int) As DateTime

You pass it your start date and the number of minutes for the deadline and it returns the deadline date/time based on the service hours of a day. If so, now you can just add the paused time to in_DeadlineMinutes or add another argument for it.

Thanks for the repsonse.

The database is SQL with an Access FE.
You are probably correct about not storing it, however the calculations for just calculating the initial deadline are already complicated, and for speed and ease of reporting later I decided to store it...

Maybe I should look at transferring all the calculations back into SQL server, which would mean a fair bit of work but probably scale better.

I guess I'm also struggling with how to calculate the current SLA based on a completely random number of stops and starts per call, an small example with my added Deadline function field would look like

plog said:

hopefully that's a function twith a signature that looks like this:

Function get_WorkDeadline(in_StartTime AS Date, in_DeadlineMinutes As Int) As DateTime

You pass it your start date and the number of minutes for the deadline and it returns the deadline date/time based on the service hours of a day. If so, now you can just add the paused time to in_DeadlineMinutes or add another argument for it.

Click to expand...

Thanks Plog - you can see from the data that it's pretty much as you described (I added the actual status text for clarity)

I do indeed have a function for the initial deadline calculation. I didn't think of reusing it/ adapting it.

It will need a little work on it, but as usual the much better eyes here have helped me see past the trees a little clearer!

Having SQL server as a backend makes things easier not to mention Faster !
Also I think you are looking at this "backwards", you dont want to count the stopped clock and add it... You simply want to add together the running time.

Can you post a sample of your status table? This is going to be KEY to getting a working solution.

The status_Id Table;
I've just added the SlaAdjust field to play around with


The Status_Tracking table;

OK, so you dont have a "previous" status field... so we will have to make it on the fly, this is the fortune of SQL server... Access wouldnt be able to do this quite as easy
For this you need to use the partition by clause
https://msdn.microsoft.com/en-us/library/ms189461.aspx

I am more comfertable with Oracle, so for SQL Server its a bit of Air code:

just an observation.

if you have a call recorded at 9.45, and the 8 hour window closes therefore at 17.45 - because this is outside the service time, I assume the window would be pushed further back to 9.45 the next day?

now if events occur during this period to push back the time further, then you need to accumulate the "lag" time, I think, and add the total lag to the window,

so taking your example, you need to record the time taken for each pause/restart, and add that time to the window close time.

that's the sort of logic I think you need.

----
so, similar thoughts to other posters

with clocking times (eg an employee time clock), I would normally suggest each clock event is a separate row. in this case I think each event should carry its own start and close, so it is easier to calculate. so when you get a pause event, the release time for the event is carried within the same row.

one issue that occurs to me is overlapping pause events.

If you get a pause event at 11am that doesn't release until 1pm. and another pause event at 12.00noon that releases at 1.30, clearly (clearly?) this shouldn't extend the service window by a total of 3.5 hours (or should it) - maybe you need to evaluate the window extension by iterating the recordset of pause events in code, rather than a simple "sum" of the pause events

you also need a way of identifying uncleared pause events - maybe a timer running a query to identify and display live pause events would be useful. It would be easy to forget to take off/resolve a pause event.

In general, dave, I would advice against counting pause times... in reality you are looking for the running time, therefor you should count the running clock, not the paused clock.

Counting is a lot more complex in the sence that you shouldnt just count the pauses in your 8 hour window but rather in the extended window.
I.e. if you have 4 hours of pause time, you want to search for any additional pauses in the 8-12 hour window. This holds another 2 hour of pause, now you need to look further to the 12-14 hour window which holds another 1 hour of pause... etc...
Instead if you simply add together the running clock events, it is a lot easer to find the total running time of an event and see if that is in or outside of SLA

True, though is your comment on each event having a start and stop time.... or having a "next event" foreign key for a self join to find the time of the next event easily.

Thanks for all the replies everyone - lots of good input.

gemma-the-husky said:

just an observation.

if you have a call recorded at 9.45, and the 8 hour window closes therefore at 17.45 - because this is outside the service time, I assume the window would be pushed further back to 9.45 the next day?

Click to expand...

Yes - Correct and I already have a function to calculate this which also takes account of the variable SLA hours and weekend / holidays etc.

so, similar thoughts to other posters

with clocking times (eg an employee time clock), I would normally suggest each clock event is a separate row. in this case I think each event should carry its own start and close, so it is easier to calculate. so when you get a pause event, the release time for the event is carried within the same row.

Click to expand...

Each event is a separate entry, but it doesn't have a start and end time, simply a datetime stamp as it is an event log. It might make sense to add an event stop time that would default to the timestamp unless it was a pause event. The next Start or Pause event could then complete the last empty Pause event time , this would take care of your next point...

one issue that occurs to me is overlapping pause events.

If you get a pause event at 11am that doesn't release until 1pm. and another pause event at 12.00noon that releases at 1.30, clearly (clearly?) this shouldn't extend the service window by a total of 3.5 hours (or should it) - maybe you need to evaluate the window extension by iterating the recordset of pause events in code, rather than a simple "sum" of the pause events.

Click to expand...

It should be paused for 1 hour (11:00 to 12:00) then another 1 hour 30 minutes - the balance of the second delay.

you also need a way of identifying uncleared pause events - maybe a timer running a query to identify and display live pause events would be useful. It would be easy to forget to take off/resolve a pause event.

Click to expand...

We already have an programmable alert timer on the call that sets a reminder to the call centre to look at the call - so for instance if we request log files we have a 60 minute alert to chase them up.

I'm going to go away with pen and paper and fresh coffee and draw the whole thing out.

namliam said:

In general, dave, I would advice against counting pause times... in reality you are looking for the running time, therefor you should count the running clock, not the paused clock.

Counting is a lot more complex in the sence that you shouldnt just count the pauses in your 8 hour window but rather in the extended window.
I.e. if you have 4 hours of pause time, you want to search for any additional pauses in the 8-12 hour window. This holds another 2 hour of pause, now you need to look further to the 12-14 hour window which holds another 1 hour of pause... etc...
Instead if you simply add together the running clock events, it is a lot easer to find the total running time of an event and see if that is in or outside of SLA

True, though is your comment on each event having a start and stop time.... or having a "next event" foreign key for a self join to find the time of the next event easily.

Click to expand...

my thinking was that there may be no "running clock" events, other than the initial query log, and even if they are, the running clock events are of no consequence. Sort of "engineer arrived on site" type events.

If the event itself has a 4 hour window, say. then the only thing that will push back the window close is a pause event. So if you have a one-hour pause, then the window close extends by another hour. That's what I was thinking. But if you have more than one pause event, which overlap, then you need to assess the effect of the overlap, in evaluating the total extension. That's where I was coming from.

Just iterate all the events associated with "error log" 1234 say, to establish whether the error was fixed within the performance window.

Hi Dave,

You have the same take as me on this - that we don't have a "running clock" as such but a deadline that may need to move. As is usually the case - I've been diverted onto another more urgent project for a few days so haven't had a chance to see the utter fail in my thinking yet...
(only a matter of time I'm sure)

There is Always a running clock... if you have an 8 hour deadline from time X.... that is where the clock starts, unless you encounter this or that event which stops the clock...
Another or someother event will cancel out this or that event and start the clock again...

namliam said:

There is Always a running clock... if you have an 8 hour deadline from time X.... that is where the clock starts, unless you encounter this or that event which stops the clock...
Another or someother event will cancel out this or that event and start the clock again...

Click to expand...

I interpreted the point as being a matter of establishing the latest time for completion of the service request - so given the start time of the service call the call needs to be completed by x.

At the end of the service call we can establish whether we were on time or late,

during the life of the call, events can happen that change the target time - the priority of the call could be changed, or, as the op said, the target time can be extended for particular reasons. (pause events). I didn't think of an event that completely restarts the 8 hour window, but it's possible.

Because of the potential for overlapping pause events, it seemed to me that the easiest way would be iterate the events for this service call (and you only need to iterate the pause events) to determine whether the target time has been extended. But because pause events can overlap, I didn't think you could just sum the pause delays in a simple query. hence I thought you would need to iterate the pause events in sequence to determine the overall extension to the target time.

I expect we are getting the same solution, in truth, and we are just discussing definitions.