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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…