Hi I'm dave and I'm new to the forum, SQL and SSRS. Thanks in advance for your assistance and patience.
I have a recall report that produces letters for every patient who needs to return to the office on a certain date based on their recall plan (a plan that determines when they should return based on their condition).
I have another table that stores all appointments for each patients (past and present). Appointments in the recall_plans table are auto-generated whereas appointments in the appointments table where created manually. The recall plan report is not checked if a person calls in to make an appointment so often the same appointment is represented in both tables resulting in duplicate reminder letters being sent out.
I need to do two things: (I know my approach is not necessarily resolving the business problem but this is what I am tasked with)
I need to produce a list showing the next appointment for each patient but only if it is in the future.
I need to add a column to the first report showing each patient’s next appointment so someone can manually identify that duplicate letters would go out for specific patients and intervene accordingly.
Recall Report Query:
SELECT description as [Plan Name], per.first_name + ' ' + per.last_name as [Patient], substring (plan_start_date, 5,2) + '-' + substring (plan_start_date, 7,2) + '-' + substring (plan_start_date, 1,4) as [Plan Start Date], substring (nr.expected_return_date, 5,2) + '-' + substring (nr.expected_return_date, 7,2) + '-' + substring (nr.expected_return_date, 1,4) as [Expected Return Date] FROM recall_plan_mstr rp, patient_recall_plans nr, patient pt, person per WHERE rp.practice_id = nr.practice_id and rp.recall_plan_id = nr.recall_plan_id and nr.practice_id = pt.practice_id and nr.person_id = pt.person_id and per.person_id = pt.person_id and (active_plan_ind = 'Y') and rp.practice_id = '0025
Recall Report Results:
PLAN NAME PATIENT START RETURN
OFFICE VISIT W/ DR Charles Span 04-18-2011 12-15-2011
select person_id, appt_date from appointments where person_id is not null group by person_id, appt_date order by person_id, appt_date desc