Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Selecting Next Appointment Date

    I have an appointments table that has all appointments a patient has ever had. I can write a simple query that displays the patient and all of their appointments like this:

    Code:
    select top 100 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
    Code:
    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	20100401
    378A281C-FAE7-43DF-BC03-00006E386680	20100311
    378A281C-FAE7-43DF-BC03-00006E386680	20100303
    378A281C-FAE7-43DF-BC03-00006E386680	20100218
    816D4D31-3C99-4762-878D-000097883B73	20110316
    816D4D31-3C99-4762-878D-000097883B73	20101216
    2682C7B9-6FD2-4A08-BD70-0000B462C648	20100726
    2682C7B9-6FD2-4A08-BD70-0000B462C648	20100524
    D3B15D79-03F2-47DD-882D-0000B9F0DFE4	20110507
    D3B15D79-03F2-47DD-882D-0000B9F0DFE4	20110208
    D3B15D79-03F2-47DD-882D-0000B9F0DFE4	20100517
    D3B15D79-03F2-47DD-882D-0000B9F0DFE4	20091022
    448A3A86-D584-447E-995D-0000C062CAEB	20110914
    448A3A86-D584-447E-995D-0000C062CAEB	20110216
    448A3A86-D584-447E-995D-0000C062CAEB	20100819
    I need a result set that includes just one row per patient with only their most most recent appointment (like the bolded rows). Then I would like to filter out all appointments that are in the past.

    Thanks!
    dave

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT TOP 100 person_id, appt_date
       FROM appointments AS a
       WHERE person_id IS NOT NULL 
          AND appt_date = (SELECT MAX(z.appt_date)
             FROM appointments AS z
             WHERE  z.person_id = a.person_id)
       GROUP BY person_id, appt_date 
       ORDER BY person_id, appt_date DESC
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2011
    Posts
    2

    Thanks!

    Beautiful! Thanks for the super fast reply Pat!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •