SQL Report Help

5 pts.
SQL Reports
SQL Server Reporting Services
SQL Server Reports
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:

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
  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.

Hi, Really hard to work this out from your query but I suspect the answer would be a correlated sub query to find the latest appointment date.


Discuss This Question: 1  Reply

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.
  • ToddN2000
    I know this is an older question so I wonder how you made out. I would have tried using a null value when joining tables to generate the mailing list. Questions still arise though. If in one table the dates are entered manually, what happens if the dates are off as little as one day. You would still send 2 mailings. Not an easy fix to an issue that appears to be a business process with maintaining two dates in different files that should really be one joined table with one set of dates.
    135,305 pointsBadges:

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.

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


Share this item with your network: