Conceptual Q: Best way to track time elapsed for ticket statuses 1

[VBAjedi]

Ok, I'm building a tech support issue tracker in Access 2003. Each ticket can be assigned a variety of statuses like "In Queue", "In Process", "Waiting For Customer Response", and "Completed". A ticket may get assigned the same status more than once (it might, for instance, be bounced back and forth between "In Process" and "Waiting For Customer Response" statuses several times).

I need to be able to track/report on how long (in total business hours and days) tickets spend in each status. As an possibly relevant aside, the list of status types is stored in a "StatusTypes" table which includes an "IsBillable" boolean field indicating whether time spent in that status is billable time, so I can calculate total billable time for a ticket as well.

My current thought is to use the AfterUpdate event of the "Ticket Status" control on my form to write a record to a StatusChangelog table (with fields: TicketID, ChangedByUserID, NewStatusID, ChangeDate, ChangeTime).

But then what? Should I also use the BeforeUpdate event of the same "Ticket Status" control to write the elapsed segment time to the last StatusChangeLog record for this ticket, or plan on trying to calculate that "segment time" value on the fly each time I need it, or?

I can think of a bunch of ways that might work for this, but none of them feel very elegant... and I hate building clunky junk! How would you approach this?

VBAjedi

 

[MajP]

I think I would simply have the fields in the log

TicketID
ChangedByUserID
StatusID
StatusStartDateTime

And only store the start of the status change

So with values like

tkt1 usr1 inqueue 1/1/2011 12:30 AM
tkt1 usr2 inProcess 1/2/2011 11:30 AM
tkt1 usr1 Waiting 1/4/2011 02:45 AM
tkt1 usr3 inProcess 1/4/2011 12:30 pM
tkt1 usr1 closed 1/6/2011 12:30 AM

You can calculate the time duration for each period in a query. This assumes your times are contigous and all times accounted for.

The duration for each record is the statusStartDatetime of the next record - the statusStartDateTime of the currentRecord. If the next record is null and not closed the then it is now() - statusStartTime. If status is closed then 0.

You can do that pretty simply in a query, but Your data set may be very large (I do not know) and that could take a long time to run. I still would use the same data structure and calculate the elapsed time in code and maybe write to the table.

 

[VBAjedi]

Ok, so it sounds like I may be on the right track. Just conceptually, though, I would have thought it would be better design practice to store the segment time-length in the table the first time I calculate it, as opposed to recalculating it every time I need it (which requires me to assume that the next record/segment is still present/unchanged, and also seems like it would take a few more microseconds per result row).

What's the potential advantage to recalculating the segment time-length every time?

VBAjedi

 

[MajP]

It may/many not be advantageous. If time and resources are not an issue it may be advantageous in that you ensure that you always have the correct information if changes are made.

If you do the calculations and store them persistently this normally would happen with some kind of form event, but what happens if the data is modified through code, a query, or a user in the backend. So you have to ensure you account for all of these cases.

"Never storing data that you can calculate" is a guideline and not a rule. In some cases the calculations take so much time and resources (or the queries so complex) you would prefer to store it once, and hope you catch all the cases where the input fields are modified or changed. So you have to weigh the tradeoffs. If you are talking tens/hundreds of thousands of records then you may be better off Trying to do it when you change status.

However, with that said. Using the proposed data structure this should work.

Return the elapsed time in minutes or seconds and then you can format it or calculate any way you want.

Public Function elapsedTime(varStatus As Variant, varNextStatus As Variant, varTime As Variant, varNextTime As Variant) As Long
  If Not IsNull(varStatus) Then
    If Not varStatus = "Closed" Then
      If IsNull(varNextTime) Then
        elapsedTime = DateDiff("n", varTime, Now())
      Else
        elapsedTime = DateDiff("n", varTime, varNextTime)
      End If
    End If
  End If
End Function

Then the query would look like

SELECT 
 tblStatusLog.TicketID, 
 tblStatusLog.StatusID, 
 tblStatusLog.StatusStartDateTime, 
 (select top 1 statusStartDateTime from tblStatusLog as A where A.TicketID = tblStatusLog.ticketID and tblStatusLog.statusStartDateTime < a.statusStartDateTime) AS StartNextStatus, 
 (select top 1 statusID from tblStatusLog as A where A.TicketID = tblStatusLog.ticketID and tblStatusLog.statusStartDateTime < a.statusStartDateTime) AS NextStatus,  
  ElapsedTime([statusID],[nextStatus],[statusStartDateTime],[startNextStatus]) AS MinutesInStatus, 
 [minutesinstatus]/60 AS HrsInStatus
FROM 
 tblStatusLog
ORDER BY 
 tblStatusLog.StatusStartDateTime;

I would not however store the end time in the current status because that is always the start time of the next status or Now if it is the last status and not closed. It is another field that you would have to ensure is always in synch.

 

[VBAjedi]

Ok, that makes sense... in general I'm not very good yet at using SQL queries to calculate values so I think I tend to gravitate towards storing them instead. But seeing how you went about this is SUPER helpful! Have a star.

I'll go chew on this for a while and come back if I have any follow-up questions.

VBAjedi

 

[MajP]

I am no SQL Jedi either, I usually get these answered in the Query forum by someone else. But I have asked these enough times that I have a few examples to work with. However, if the query gets too involved or too expensive in time/resource, I still do not do the calculation at the form level each time I modify a record. Instead I usually would run the whole table at once prior to using it.
So assuming I had a StatusDuration field for each record, prior to using a query that uses that field I would ensure that all data is updated. I would loop the table and update that field. That way if there was changes done to the underlying table that effect the calculated duration I will catch it all at once. This is kind of a comprimise between the two methods. Like a dynamic query you ensure that your calculation are current, but you can save it persistently for ease of use and resource savings. Especially when you are comparing records to previous records in big recordsets, it is often faster than a SQL solution.

To ensure I am not continually updating the entire table in a single session, I store the last time I ran the code on the table. Then compare that value to the greatest statusChange date.

 

[VBAjedi]

In my less-than-expert opinion, that's pretty doggone clever. Have another star for adding another trick to my toolbag... I've got a version of your sample function/query up and running nicely, but in situations where that isn't practical/speedy, I'll use this hybrid approach (unless/until someone else shows me something even MORE clever.) I'm a sucker for a really well-written query or line of code. LOL

Thanks for taking the time to pass on what you know!

VBAjedi

 

[MajP]

so here is a general approach of looping through
1)order by ticket and then statusDate
2)loop through the recordset
3)get the information on the current record
4)move to the next record and get its information
5)do your calculations with the current record and the next record checking if you are still on the same ticket
6)move back to the current record
7)Update the current record duration
8)move to the next record and continue looping until eof.

Public Sub updateStatusDuration()
  Dim rs As DAO.Recordset
  Dim currStatus As Variant
  Dim currTime As Variant
  Dim nextTime As Variant
  Dim currTicket As Variant
  Dim nextTicket As Variant
  Dim lngElapsedTime As Long
  Dim strSql As String
  
  strSql = "Select * from tblStatusLog order by TicketID, StatusStartDateTime"
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  Do While Not rs.EOF
    nextTime = Null
    nextStatus = Null
    nextTicket = Null
    lngElapsedTime = 0
    'Get info on  current record
    currStatus = rs!statusID
    currTime = rs!StatusStartDateTime
    currTicket = rs!ticketID
    'move to next record and make sure did not go beyond the last record
    rs.MoveNext
    If Not rs.EOF Then
      nextTicket = rs!ticketID
      'if it is still the same ticket get the next time
      'Or leave it as null
      If currTicket = nextTicket Then
        nextTime = rs!StatusStartDateTime
      End If
    End If
    'use the old function to calculate the elapsed time
    If Not IsNull(currStatus) Then
       If Not currStatus = "Closed" Then
         lngElapsedTime = elapsedTime(currStatus, currTime, nextTime)
       End If
      End If
     'move back to the original record so you can update it
     rs.MovePrevious
     rs.Edit
       rs!statusDurationMinutes = lngElapsedTime
     rs.Update
     'now move to the next record.
     rs.MoveNext
    Loop
End Sub

You should be able to update thousands of records in no time. I made this somewhat generic, but it could be tweaked to be faster.