date and time calculation
Posted: Fri Mar 31, 2006 3:14 pm
I have been trying to wrap my head around this for the past couple hours and can't seem to come up with a query/method to properly get the numbers I need!
heres the deal.
We need to calculate time spent on "tickets" by our customer service people, average times for responses, closings, etc.
this is easy... no problem... BUT the numbers aren't accurate... they reflect a 24 hour day, we work 8. Therefore a ticket that comes in at 5:55PM wouldn't get tended to till 8AM the next day... this causes a 14 hour spike in user inactivity, thus making their averages ungodly inaccurate, even worse would be a ticket that comes in at 5:55PM on a Friday... this would give it close to 36 hours of calculated time when workers couldn't tend to the ticket but its still being tallied against their totals. Now .. I need to find a way to calculate time while ignoring or subtracting the hours we know we aren't not in the office.
I was thinking do it within the PHP because I can't even begin to think of how I would work that into a query.
Anyone have any suggestions on a plan of attack?
---- some useful information ----
times/dates are stored in unix_time
creation date and last activitiy date are the primary dates used to calculate average, but all activities are stored in a log that can be accessed
heres the deal.
We need to calculate time spent on "tickets" by our customer service people, average times for responses, closings, etc.
this is easy... no problem... BUT the numbers aren't accurate... they reflect a 24 hour day, we work 8. Therefore a ticket that comes in at 5:55PM wouldn't get tended to till 8AM the next day... this causes a 14 hour spike in user inactivity, thus making their averages ungodly inaccurate, even worse would be a ticket that comes in at 5:55PM on a Friday... this would give it close to 36 hours of calculated time when workers couldn't tend to the ticket but its still being tallied against their totals. Now .. I need to find a way to calculate time while ignoring or subtracting the hours we know we aren't not in the office.
I was thinking do it within the PHP because I can't even begin to think of how I would work that into a query.
Anyone have any suggestions on a plan of attack?
---- some useful information ----
times/dates are stored in unix_time
creation date and last activitiy date are the primary dates used to calculate average, but all activities are stored in a log that can be accessed