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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply