How to do the following (may require correlated sub-query)

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

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


    - Craig Farrell

    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

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

  • 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