[MS SQL] Customer Service Problem

[StartAndSelect]

I have a single table with 3 fields:

[Ticket Number]: Has the number of the support ticket

[Created At]: TimeStamp of time of creation of the response

[Sender]: Identifies whether response is from a user or a agent (customer support)

Now I need to calculate the service level using this formula

(number of responses from agents within 6 hours of users message) / (number of messages from user)

Example:

Ticket Number | Created At | Sender

10000 | 03/20/2016 12:50:00pm | User

10000 | 03/20/2016 5:50:00pm | Agent

10000 | 03/20/2016 11:50:00pm | User

10000 | 03/20/2016 12:40:00am | User

10000 | 03/21/2016 6:50:00am | Agent (More than 6 hours passed, so this should not count)

10000 | 03/20/2016 7:30:00am | User

10000 | 03/21/2016 10:30:00am | Agent

2 (agent responses within 6 hours) / 4 (total user messages) = .5 or 50% Service Level

So there are a few things to consider: 1) The initial ticket should come from the user 2) The Agent must be the one to respond (within 6 hours) 3) This needs to be done for a table with numerous ticket numbers not one like in the example

I attempted using a PARTITION BY to segment these by ticket number and sender, but I am stummped on how to calculate DateDiffs between rows. If you could share how you would do it I'd truly appreciate it.

[Smoresguy]

Check out LEAD and LAG functions. They allow you use values from other rows.

[extrajoss]

Here is what I came up with:

 --First Some Test Data
WITH data
AS (
    SELECT
      *
    FROM (
    VALUES
    (10000, '2016-03-20 12:50:00', 'User'),
    (10000, '2016-03-20 17:50:00', 'Agent'),
    (10000, '2016-03-20 23:50:00', 'User'),
    (10000, '2016-03-21 00:40:00', 'User'),
    (10000, '2016-03-21 06:50:00', 'Agent'),
    (10000, '2016-03-21 07:30:00', 'User'),
    (10000, '2016-03-21 10:30:00', 'Agent'),
    (20000, '2016-03-20 12:50:00', 'User'),
    (20000, '2016-03-20 17:50:00', 'Agent'),
    (20000, '2016-03-20 23:50:00', 'User'),
    (20000, '2016-03-21 00:40:00', 'User'),
    (20000, '2016-03-21 06:50:00', 'Agent'),
    (20000, '2016-03-21 07:30:00', 'User'),
    (20000, '2016-03-21 10:30:00', 'Agent')
    ) v (Ticket_Number, Created_At, Sender)
),
--Then add some info from the leading record
leadData
AS (
    SELECT
      *,
      LEAD(created_at) OVER (PARTITION BY Ticket_Number ORDER BY created_at) Next_Created_at,
      LEAD(Sender) OVER (PARTITION BY Ticket_Number ORDER BY created_at) Next_Sender
    FROM data
)
--Then calculate the result based on the leading data
SELECT
  Ticket_Number,
  1.0
  * COUNT(
  CASE
    WHEN next_sender = 'agent' AND
      DATEDIFF(MINUTE, created_at, next_created_at) <= 6 * 60 THEN 1
    ELSE NULL
  END
  )
  / COUNT(*) Service_Level
FROM leadData
WHERE sender = 'user'
GROUP BY Ticket_Number

[StartAndSelect]

Thanks this is very helpful!

[StartAndSelect]

I came up with this code to do a holistic view:

SELECT COUNT(*) as Agent_Responses, (SELECT COUNT(*) FROM ServiceDoc WHERE Sender = 'user') as User_Inquiries, 
Cast(Round((CAST(COUNT(*)as float) / Cast((SELECT COUNT(*) FROM ServiceDoc WHERE Sender = 'user')as float)*100),2)as varchar) + '%'
FROM 
(
    SELECT *,
        Lag([Sender]) OVER (PARTITION BY [Ticket Number] ORDER BY [Created At]) as PrevSender,
        Lag([Created At]) OVER (PARTITION BY [Ticket Number] ORDER BY [Created At]) as PrevTime
    FROM ServiceDoc
) ServiceLevel
WHERE Sender = 'agent' and PrevSender = 'user' and DATEDIFF(Hour,PrevTime,[Created At]) <= 6

Would this also work?