SQL determine whether a customer issue is answered by the employee

Question

I'm looking for a way to determine whether an employee has fixed the problem directly for a customer. When an employee needs to return within 7 days, the problem was not solved.

Situations:

• The problem was solved immediately. Thus a positive score.
• Another employee has already tried to fix, but failed. For this employee still counts he fixed it immediately.
• The employee has returned to the customer and fixes the problem. No score since he has failed before.
• The employee has visited the customer, but a colleague had to visit to finally fix. Thus no score.

The dataset looks like this, I have faked the outcome:

Customer    Date        Employee    Outcome_Fixed
1           1-8-2018    1           1
2           2-8-2018    4           0
2           8-8-2018    3           0
2           11-8-2018   4           0
3           3-8-2018    8           0
3           5-8-2018    7           1
4           6-8-2018    3           1

I need to be calculating that last column. Based on the customer, employee and date difference within or over 7 days. Anyone a clue on how to solve this? Been trying to use lead and lag, but since the amount of visits is unknown this failes.

Thanks!

Answer 1

CASE WHEN EXISTS is one way to do this:

SELECT Customer,
       [Date],
       Employee,
       CASE WHEN EXISTS (SELECT * 
                         FROM yourTable T2
                         WHERE T.[Date] < T2.[Date]
                         AND DATEDIFF(DAY, T.[Date], T2.[Date]) < 8
                         AND T.Customer = T2.Customer)
            THEN 0
            WHEN EXISTS (SELECT *
                         FROM yourTable T2
                         WHERE T.Employee = T2.Employee
                         AND T.Customer = T2.Customer
                         AND T.[Date] <> T2.[Date])
            THEN 0
            ELSE 1
       END Outcome_Fixed
FROM yourTable T

This first checks to see if anyone has visited the same customer in the next 7 days. If so 0, otherwise 1. It then checks to make sure the same employee has not failed before. I believe that covers all of your conditions.