March 30, 2011 at 1:35 pm
Hey all,
I have a table that lists the appointments of patients at a doctor's office (past, future, and cancelled appointments)--patient ID, the date of the appointment, and the status of the appointment.
I want a query that gives me the IDs of patients that have a canceled appointment since 1/1/2010, but does not have a subsequent pending appointment or completed appointment after that date).
I'm not sure how to do this. I know how to get a list of patients that have a cancelled appointment since 1/1/2010, but how would I exclude patients that have a pending or completed appointment only AFTER the date for which the patient canceled the appointment?
For example, if a patient had a cancelled appointment on 1/1/2010 and a completed appointment on 1/2/2010, I would not want them on the list. However, if a patient had a completed appointment on 1/1/2010 and a cancelled appointment on 1/2/2010, I would want them on the list.
Can anyone give me some advice on how to go about doing this? As I understand it, it would involve a correlated sub-query. My understanding of the differences between inner joins and correlated sub-queries is not that rigorous, so I was hoping to use this task as a way to learn more about these issues. I would greatly appreciate any help anyone can give. Please let me know if you have any questions about the task described.
Thanks.
March 30, 2011 at 1:44 pm
Short and easy form:
Yes, you want a subquery (not a correlated one) that will go in and find the max() date for each patientID, filtered only on dates after x time (1/1/2010 in your case).
You then rejoin that result to the master list, so you reduce the list's entries via the join to only the last appointment. Filter that result set on cancels, and there you go.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 30, 2011 at 1:54 pm
Here's an idea that I think is close to Craig's suggestion:
SELECT
*
FROM
dbo.appointments AS A
JOIN (
SELECT
patient_id,
MAX(appointment_date) AS max_appt_date
FROM
dbo.appointments AS A
GROUP BY
patient_id
) AS max_appt
ON A.patient_id = max_appt.patient_id AND
A.appointment_date >= max_appt.max_appt_date
WHERE
A.STATUS = 'Cancelled'
If you read the top article linked in my signature it will help you write posts that will get you better solutions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 3:19 pm
Ahh, that makes sense. I like the joining of the max appdate as a created field. Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply