问题描述:

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

网友答案:

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
相关阅读:
Top