For our site, the issues are like this:
1. HD person takes call, auto-assigns ticket number.
2. If first call for this customer, HD person sidesteps to ask for customer name, phone, affiliation (how is this person authorized to call for help in the first place; usually a department code or branch/department code from a drop-down). Person's name is stored for use in subsequent dropdown/autofill, autogens a customer number.
3. HD person takes data, asks enough questions to assign class of problem (for us, it is by specific software projects + a hardware class + a desktop class + a couple of other classes that only the US Dept. of Defense understands fully.) A new OPEN ticket is created automagically and is owned by the HD person.
4. HD person can do one of a few things:
- forward ticket to tech. dispatcher (or, in your small-shop case, act as dispatcher and dispatch directly to tech) - FORWARD is key but ticket status is automatically on HOLD until targeted person resumes the ticket.
- resolve the problem with verbal advice / instructions - CLOSE
- put problem on hold pending something needing to be done - HOLD is key
- HD person cannot let go of ticket while the ticket is in the OPEN state. Therefore, one of the other states must be asserted. As a a safety factor, TWO tables are updated.
One shows what happened to the ticket: FWD, CLS, HLD, OPN, RES (resume a HOLD ticket), REO (re-open a ticket), OWN (assume ownership of someone else's ticket - so it won't go dangling if the targeted person does something so unkind as to die on company time...), ADW (add work history entry)
When FORWARD is chosen, HD person has option of WHY forwarding: To TECH, To MGMT (for policy decision), To (any of the other shops where something is done or a decision has to be made).
When HOLD is chosen, HD person must show why it is on hold - PARTS (need something physical), NETWK (connectivity issue precludes action), FUNDS (needs funding source identified. In our case, might not actually need cash - just needs cash account number for chargeback), INFO (caller needs to provide supplemental info), etc.
Also, when closing, holding, or forwarding, the work history has to be updated with a short comment. Some of the items, however, can be automatically updated in the detailed history. For instance, REOPEN and RESUME require no more explanation.
Every so often, something gets run that marks old tickets in a way that suggests their priority should be boosted. In our shop this is "automated problem escalation."
Then there are the reports. Gazillions of reports about how much time was spent in each state. This is why you want the event table to time-tag each open-class event and each close-class event. You can then take the DATEDIFF of the two times and accumulate the minutes (or even seconds, if you are a REAL glutton for punishment) on the particular problem.
We have found a couple of commercial apps that do what we want so we aren't using the old database we started for this very project. No need to reinvent the wheel.
You might also check on the web (the WHOLE web) for professional organizations for Help Desk or Customer Service Center groups. They might link you to some really useful articles. I know our local org is a member of the national organization but I also know there is more than one such org.
