May 31, 2011 at 5:11 pm
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 NAMEPATIENTSTARTRETURN
OFFICE VISIT W/ DRCharles Span04-18-201112-15-2011
LIPID PANELRonald Chap04-11-201106-28-2011
OFFICE VISIT W/ DRRonald Chap 04-11-201104-21-2011
OFFICE VISIT W/ DRWill Thor03-31-201102-01-2012
PACEMAKER CHECKSylvia Berkly05-03-201108-03-2011
OFFICE VISIT W/ DRTim Cayle04-13-201109-26-2011
OFFICE VISIT W/ DRCaferana Mercade04-11-201110-08-2011
OFFICE VISIT W/ DRSusanna Calter05-10-201105-07-2012
ICD CHECKJim Southern04-14-201107-13-2011
STRESS ECHODon Cobey04-28-201106-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-00006CB5A43320091228
073C8F83-CE15-4192-8E12-00006CB5A43320090510
073C8F83-CE15-4192-8E12-00006CB5A43320090301
073C8F83-CE15-4192-8E12-00006CB5A43320081006
378A281C-FAE7-43DF-BC03-00006E38668020110509
378A281C-FAE7-43DF-BC03-00006E38668020110217
378A281C-FAE7-43DF-BC03-00006E38668020110124
378A281C-FAE7-43DF-BC03-00006E38668020110111
378A281C-FAE7-43DF-BC03-00006E38668020101207
816D4D31-3C99-4762-878D-000097883B7320110316
816D4D31-3C99-4762-878D-000097883B7320101216
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
P.S. Ok so it looks like all my formatting was a waste of time... sorry that everything is all smooshed together.
June 1, 2011 at 10:43 am
Anybody... anybody..... Bueller?
June 1, 2011 at 1:14 pm
One option for your first question is this:
select person_id, MAX(appt_date)
from appointments
where person_id is not null
and appt_date > GETDATE()
group by person_id
order by person_id, appt_date desc
By grouping only on the person_id you will only get one row per person. You then have to use an aggregate function on the appt_date. Max will return the furthest in the future, min the most recent. Add the where clause to only return appt_date > today so you don't get old dates.
June 1, 2011 at 1:34 pm
Here's a shot at question 2, to join them together. I changed your table JOIN syntax to use the more conventional SQL syntax. I then used the first query as a dynamic table aliased as ap and JOINed it to the orginail query. I didn't load data and actually test that it would return results, so there may be other issues:
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 [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, MAX(appt_date) AS appt_date
from appointments
where person_id is not null
and appt_date > GETDATE()
group by person_id
) AS ap
ON per.person_id = ap.person_id
WHERE (active_plan_ind = 'Y')
AND rp.practice_id = '0025'
June 1, 2011 at 2:52 pm
Hi sdvoranchik,
Thank for your reply. I tried your suggestion but it failed because appt_date was still included in the order by. Once I removed it it returned the expected results. THX!
I was also able to accomplish it with this query, but I like yours better.
SELECT person_id, 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
ORDER BY person_id, appt_date DESC
Thanks,
dave
sdvoranchik (6/1/2011)
One option for your first question is this:select person_id, MAX(appt_date)
from appointments
where person_id is not null
and appt_date > GETDATE()
group by person_id
order by person_id, appt_date desc
By grouping only on the person_id you will only get one row per person. You then have to use an aggregate function on the appt_date. Max will return the furthest in the future, min the most recent. Add the where clause to only return appt_date > today so you don't get old dates.
June 1, 2011 at 4:24 pm
You are the Man... THX!!!
I tweaked your query a bit and it works perfectly:
SELECTdescription 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 =
(SELECTMAX(z.appt_date)
FROMappointments z
WHEREz.person_id = a.person_id)
ANDappt_date >= getdate()
GROUP BY person_id, appt_date
) AS ap
ON per.person_id = ap.person_id
WHERE (active_plan_ind = 'Y')
ANDrp.practice_id = '0025'
ANDnr.expected_return_date >= getdate()
sdvoranchik (6/1/2011)
Here's a shot at question 2, to join them together. I changed your table JOIN syntax to use the more conventional SQL syntax. I then used the first query as a dynamic table aliased as ap and JOINed it to the orginail query. I didn't load data and actually test that it would return results, so there may be other issues: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 [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, MAX(appt_date) AS appt_date
from appointments
where person_id is not null
and appt_date > GETDATE()
group by person_id
) AS ap
ON per.person_id = ap.person_id
WHERE (active_plan_ind = 'Y')
AND rp.practice_id = '0025'
June 1, 2011 at 4:32 pm
Glad it all worked out... Good luck
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply