SQL select follow up values based on initial select

Question

I have the following data:

  Client_id     Call_started               Call_ended                 Outcome_id      
 ----------- -------------------------- -------------------------- ---------------- 
  111         Aug. 21, 2018, 4:10 p.m.   Aug. 21, 2018, 4:13 p.m.   Rescheduled     
  111         Aug. 22, 2018, 1:00 p.m.   Aug. 22, 2018, 1:10 p.m.   Rescheduled     
  112         Aug. 21, 2018, 3:10 p.m.   Aug. 21, 2018, 3:11 p.m.   Rescheduled     
  111         Aug. 22, 2018, 5:00 p.m.   Aug. 22, 2018, 5:08 p.m.   Interested      
  113         Aug. 22, 2018, 1:00 p.m.   Aug. 22, 2018, 1:10 p.m.   Rescheduled     
  114         Aug. 21, 2018, 2:10 p.m.   Aug. 21, 2018, 2:11 p.m.   NotReachable   
  112         Aug. 22, 2018, 9:10 a.m.   Aug. 22, 2018, 9:20 a.m.  NotInterested  
  113         Aug. 22, 2018, 5:20 p.m.   Aug. 22, 2018, 5:25 p.m.   Interested

Below is the SQL query for client calls which got rescheduled

Select a.client_id, a.call_start,a.call_end, a.outcome_id
From client_analysis a
where a.outcome_id like %Rescheduled%'

Now I also want to select what happened to follow-up rescheduled calls (were they called on time, etc). How can I select other calls made (or next a.call_start) to the given client_ids where a.outcome_id is rescheduled (next a.call_start < rescheduled a.call_end)?

Below is the expected output:

 Client_id     Call_started               Call_ended                 Outcome_id      
 ----------- -------------------------- -------------------------- ---------------- 
  111         Aug. 21, 2018, 4:10 p.m.   Aug. 21, 2018, 4:13 p.m.   Rescheduled     
  111         Aug. 22, 2018, 1:00 p.m.   Aug. 22, 2018, 1:10 p.m.   Rescheduled     
  111         Aug. 22, 2018, 5:00 p.m.   Aug. 22, 2018, 5:08 p.m.   Interested      
  112         Aug. 21, 2018, 3:10 p.m.   Aug. 21, 2018, 3:11 p.m.   Rescheduled     
  112         Aug. 22, 2018, 9:10 a.m.   Aug. 22, 2018, 9:20 a.m.  NotInterested  
  113         Aug. 22, 2018, 1:00 p.m.   Aug. 22, 2018, 1:10 p.m.   Rescheduled     
  113         Aug. 22, 2018, 5:20 p.m.   Aug. 22, 2018, 5:25 p.m.   Interested

Call_start are in the same columns for a given client_id and based on start time we can figure out the sequence of the calls made.

ideally the table would have an extra column so that "follow-up" calls would contain the ID of the previous call. Then you could easily join the table to itself to trace the sequence of calls. — ADyson
– ADyson, Commented Sep 4, 2018 at 10:19
Not exactly, there is a call_id which is specific to individual call. But to trace to whom the call_id is assigned is through client_id. To find which call_id or call was made first is through the timestamp for the call (call_started). — Dsh M
– Dsh M, Commented Sep 4, 2018 at 10:38
My point was, can you amend the data structure so that you've got such a column? Would make the problem quite a bit easier to solve. — ADyson
– ADyson, Commented Sep 4, 2018 at 10:45
What you're saying is right about the data structure, but @DshM's problem can be solved through LEAD and PARTITION BY — SuperSecretAndNotSafeFromWork
– SuperSecretAndNotSafeFromWork, Commented Sep 4, 2018 at 10:49
@ADyson Oh yes I totally agree with you. Instead of having as separate rows if I get the FU call as another column that would make it more logical to follow. — Dsh M
– Dsh M, Commented Sep 4, 2018 at 11:06

SuperSecretAndNotSafeFromWork · Accepted Answer · 2018-09-04 10:48:12Z

1

This can be done by looking up the next call for the customer that meets a condition (where the call is rescheduled, lookup the next call)

This uses two concepts, LEAD - lookup next value; and CASE WHEN - the condition for when to apply the LEAD

Try this SQL:

CASE WHEN Outcome_ID = 'Rescheduled' 
    THEN LEAD(Call_Started) OVER (PARTITION BY Client_ID ORDER BY Call_Started ASC) 
        END AS next_call_start_time

To explain line by line:

CASE executes the statement only when the row with column Outcome_ID is equal to Resceduled,

If rescheduled, LEAD looks up the Call_Started value, for the Client_ID's ( think of PARTITION BY similar to GROUP BY) next call (ORDER BY Call Started ASC)

If you wanted to put a different value from another column in this new column, like the next call's end date, replace LEAD(Call_Started) with LEAD(Call_Ended) etc

edited Sep 4, 2018 at 10:48

answered Sep 4, 2018 at 10:39

SuperSecretAndNotSafeFromWork

1,3012 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SuperSecretAndNotSafeFromWork Over a year ago

For the calls that aren't rescheduled, the new column will have a null value, unless you put in an ELSE statement in the CASE WHEN

Collectives™ on Stack Overflow

SQL select follow up values based on initial select

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related