SQL Query - Complex, not too sure where to start

Question

My apologies about the crappy title, but I am at a complete lost of where to start with this one

I have two tables The first table just has customer ids the second table has a list of interactions we have had with each customer This includes interactions pertaining to appointments as well as a bunch of interaction types, most of which we don't call about

For each customer, we want to the date & description of their last appointment related interaction (whether it is canceled, scheduled, or had) as well as the date of the last prescription refill, if it exists

Table_1

User_ID    
-------
  001 
  002 
  003

Table_2

User_ID    Date         Interaction
------------------------------------------------------
  001       1/21/2015   Canceled Appointment
  001      12/09/2014   Scheduled Appointment
  001      12/01/2014   Called to Refill Prescription
  002       2/1/2015    Had Appointment
  002      12/2/2014    Called About Symptoms
  002      11/18/2014   Scheduled Appointment
  003       2/1/2015    Called to Refill Prescription
  003      11/28/2014   Had Appointment

Desired output

User_ID   Date         Appointment_Details      Prescription Refill Date
------------------------------------------------------------------------
 001       1/21/2015   Canceled Appointment     12/01/2014
 002       2/1/2015    Had Appointment          n/a
 003      11/28/2014   Had Appointment          2/1/2015

Answer 1

Best to solve this in three steps:

For each User_ID, select the last record where the interaction is in the list "Cancelled Appointment", "Scheduled Appointment", or "Had Appointment". To do this we can use a Correlated Subquery to find the max(date) for each user_id where the interaction is in that list.

SELECT 
    user_id,
    DATE,
    interaction
FROM table_2 t2
WHERE DATE IN (
        SELECT max(DATE)
        FROM table_2
        WHERE t2.user_id = user_id
            AND Interaction IN (
                'Cancelled Appointment',
                'Scheduled Appointment',
                'Had Appointment'
                )
        )

For each User_ID, select the record where the interaction is "Called to refill Prescription". This one is a bit simple since need only to filter for "Called to refill prescription".

SELECT user_id, date FROM table_2 WHERE Interaction = 'Called To Refill Prescription'

Now we just join them together using a LEFT OUTER JOIN, so we pick up all records from the first recordset, and only those that are matching from the second record set

SELECT
    t1.user_id,
    t1.date
    t1.Interaction as Appointment_Details
    t2.date as Prescription_Refill_Date
FROM
    (
        SELECT 
            user_id,
            DATE,
            interaction
        FROM table_2 t2
        WHERE DATE IN (
                SELECT max(DATE)
                FROM table_2
                WHERE t2.user_id = user_id
                    AND Interaction IN (
                        'Cancelled Appointment',
                        'Scheduled Appointment',
                        'Had Appointment'
                        )
                )   
    ) t1
    LEFT OUTER JOIN (SELECT user_id, date FROM table_2 WHERE Interaction = 'Called To Refill Prescription') t3
        ON t1.user_id = t3.user_id

The big caveat here is that his really only works if the user_id shows up with on full set (a series of scheduled/cancelled/had appointments and a single "Called to refill Prescription"). Things will get a bit messier if it's necessary to show a user_id through multiple visits and multiple prescription refills. Also, if the scheduled and cancelled or scheduled and had an appointment on the same date, you will get duplicates back. This is because there is no logic stated that says which thing happened last. Either way, this should get you in the ballpark.