Adding Next Appointment Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Adding Next Appointment Date

Author

Topic

Blowinup
Starting Member

6 Posts

Posted - 2011-06-01 : 13:18:19

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
LIPID PANEL Ronald Chap 04-11-2011 06-28-2011
OFFICE VISIT W/ DR Ronald Chap 04-11-2011 04-21-2011
OFFICE VISIT W/ DR Will Thor 03-31-2011 02-01-2012
PACEMAKER CHECK Sylvia Berkly 05-03-2011 08-03-2011
OFFICE VISIT W/ DR Tim Cayle 04-13-2011 09-26-2011
OFFICE VISIT W/ DR Caferana Mercade 04-11-2011 10-08-2011
OFFICE VISIT W/ DR Susanna Calter 05-10-2011 05-07-2012
ICD CHECK Jim Southern 04-14-2011 07-13-2011
STRESS ECHO Don Cobey 04-28-2011 06-07-2010

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 14:09:15

I think we can work something out for you. But to make sure we're all in the same page, could you read this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
and re-post with all the necessary info

Thanks,

JIm

Everyday I learn something that somebody else already knew

Blowinup
Starting Member

6 Posts

Posted - 2011-06-01 : 17:57:47

Thanks guys but I got it:

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],
ap.appt_date as [Next Appointment Date]
FROM recall_plan_mstr rp
JOIN patient_recall_plans nr
ON rp.practice_id = nr.practice_id
AND rp.recall_plan_id = nr.recall_plan_id
JOIN patient pt
ON nr.practice_id = pt.practice_id
AND nr.person_id = pt.person_id
JOIN person per
ON per.person_id = pt.person_id
JOIN
(
SELECT person_id,
substring (appt_date, 5,2) + '-' +
substring (appt_date, 7,2) + '-' +
substring (appt_date, 1,4) as appt_date
FROM appointments a
WHERE person_id IS NOT NULL
AND appt_date =
(SELECT MAX(z.appt_date)
FROM appointments z
WHERE z.person_id = a.person_id)
AND appt_date >= getdate()
GROUP BY person_id, appt_date
) AS ap
ON per.person_id = ap.person_id
WHERE (active_plan_ind = 'Y')
AND rp.practice_id = '0025'
AND nr.expected_return_date >= getdate()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources