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
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