SQL Report Help

5 pts.
Tags:
Reporting
SQL
SQL Reports
SQL Server Reporting Services
SQL Server Reports
SSRS
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)
  1. I need to produce a list showing the next appointment for each patient but only if it is in the future.
  2. 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
Appointments Query:
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
Appointments Results:
073C8F83-CE15-4192-8E12-00006CB5A433 20091228 073C8F83-CE15-4192-8E12-00006CB5A433 20090510 073C8F83-CE15-4192-8E12-00006CB5A433 20090301 073C8F83-CE15-4192-8E12-00006CB5A433 20081006 378A281C-FAE7-43DF-BC03-00006E386680 20110509 378A281C-FAE7-43DF-BC03-00006E386680 20110217 378A281C-FAE7-43DF-BC03-00006E386680 20110124 378A281C-FAE7-43DF-BC03-00006E386680 20110111 378A281C-FAE7-43DF-BC03-00006E386680 20101207 816D4D31-3C99-4762-878D-000097883B73 20110316 816D4D31-3C99-4762-878D-000097883B73 20101216
Questions:
  1. How can I produce a list from the appointments table that results with one patient per row with only the latest appointment that is in the future? Do I need to write a cursor for that?
  2. How can I comingle this list into my recall report so it has a column to the right of return column that displays the patient’s next appointment date (future only)?
Both tables have a person number GUID. I hope I have adequately explained and provided enough information. If any additional information is needed please don’t hesitate to ask. Thanks, dave

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following