SQL - Join two tables and return one row only contain the latest time from the second table

Question

The tables I am working on contain details about their treatment and details abour appointments, named vwTreatmentPlans and vwAppointmentDetails respectively.

My aim is to return one row per patient code only. I want it to display two columns: the patient code from the vwTreatmentPlans table and the appointmentDateTimevalue from the vwAppointmentDetails table. MOST IMPORTANTLY, wherever there is more than one appointment row, I want only the latest appointment details to be displayed, hence:

vA.appointmentDateTimevalue Desc

Using the AND clauses, only one row per PatientCode is returned which is what I want. However, there is problem of a many to one relationship between patient codes from the two tables.

SELECT
    vT.PatientCode, MAX(vA.appointmentDateTimevalue)
FROM vwTreatmentPlans vT
    INNER JOIN vwAppointmentDetails vA ON vT.PatientCode = vA.patientcode
WHERE
    vT.PatientCode IN ( 123)
AND
    vT.[Current] = 1
AND
    vT.Accepted = 1
GROUP BY vT.PatientCode, vA.appointmentDateTimevalue
ORDER by vT.PatientCode, vA.appointmentDateTimevalue Desc

For example, one patient code returns the following output:

PatientCode   appointmentDateTimevalue
123           2016-02-01 09:10:00.000
123           2016-01-07 09:15:00.000
123           2015-12-31 10:40:00.000

So for the above example, I want this output:

PatientCode   appointmentDateTimevalue
123           2016-02-01 09:10:00.000

If there were more than one patient code being selected, I would want:

PatientCode   appointmentDateTimevalue
123           2016-02-01 09:10:00.000
456           2016-04-11 15:45:00.000

I've tried messing around with nested selects, having clauses etc. and frankly haven't a clue. I would really appreciate some help with something that must be disappointingly simple!

Thanks.

Answer 1

Why are you grouping by vA.appointmentDateTimevalue? You needn't do it. So you can get your result set with next query

SELECT
    vT.PatientCode, 
    MAX(vA.appointmentDateTimevalue) as max_date
FROM vwTreatmentPlans vT
    INNER JOIN vwAppointmentDetails vA ON vT.PatientCode = vA.patientcode
WHERE vT.[Current] = 1
         AND vT.Accepted = 1
GROUP BY vT.PatientCode
ORDER BY vt.patientCode