SQL Service Level Query

Question

I am teaching myself SQL and came across this service level question and was stumped. I was hoping for some assistance on it.

I am given a table with three fields.

TicketNumber (Number) ex. 53055 
CreatedAt (timestamp field) ex 2015-09-16 12:47
Sender (User or Agent) ex User

The goal is to calculate service level completion. A ticket is created by a user and given a number, and a response by an agent must be given within 6 hours.

Using the formula:

n_agents_reply_back_to_user_within_6hrs / n_contacts_from_user

Now I understand that the denominator in this formula is simply

Select COUNT(Sender)
From Service_Table
Where Sender Like 'User'

The numerator is giving me a lot of issues, and I was hoping someone could walk me through it. I understand I need to identify rows with the same Ticket Number, identify what time the user sent the ticket, and identify what time the agent responded and do a difference of it, and if it is <=6 then count it, otherwise don't.

As a beginner I am having quite a bit of trouble grasping how to write such a query. Any help is appreciated. Thank you

u can write two separate query for each user and Agent and then join based on Ticket number. and then one outer query to apply formula. Are you using plsql ?? or Mysql — Avi, Mar 01 '16 at 02:04
I am using mysql, but I am confused as to how to write a query that checks if the ticket number is the same, then if the first is a user followed by an agent, and lastly doing a check if the difference between their createdat fields is <=6. — dnasim, Mar 01 '16 at 02:07
There's nothing in that table to tell you when they reponded so how can you work out how long it took? — Nick.Mc, Mar 01 '16 at 02:42
The CreatedAt field is a timestamp, you use that. The ticket number tells you what the ticket is and the sender field identifies if it was the user or an agent who responded. So the first record will always be a user, the second should be the agent (unless the user sends another message) — dnasim, Mar 01 '16 at 02:59

score 0 · Answer 1 · answered Mar 01 '16 at 02:59

I am not sure what exactly you are trying to achieve but you can start with something like this.

select user.TicketNumber,user.CreatedAt-agent.CreatedAt from 
    (Select TicketNumber ,CreatedAt ,Sender from Service_Table user
    Where Sender Like 'User') user left outer join 
    (Select TicketNumber ,CreatedAt ,Sender from Service_Table agent Where Sender Like 'Agent') agent
    on  user.TicketNumber  =agent.TicketNumber

SQL Service Level Query

1 Answers1