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

@Thorsten Kettner

This is actually something similar to what I came across very soon afterwards so you're reply was perfectly timed! In the end I came up with the code below. It's not particularly elegant as I couldn't find a way of getting around having to use aggregate functions unnecessarily:

SELECT
    vT.PatientCode,
    vA.NextAppointmentDate,
    DefProvInis,
    UDAs
FROM
(
    SELECT PatientCode, MAX(TPNumber) as TPNumber, MIN(DefaultProviderInitials) as DefProvInis, MAX(UDAS) as UDAs
    FROM vwTreatmentPlans
    WHERE PatientCode in (
        1000001
        ,24001841
        ,20032285
        )
    AND [Current] = 1
    AND Accepted = 1
    GROUP BY PatientCode
) vT
INNER JOIN
(
    SELECT PatientCode, MAX(appointmentDateTimevalue) as NextAppointmentDate
    FROM vwAppointmentDetails
    GROUP BY PatientCode
) vA ON vA.PatientCode = vT.PatientCode
ORDER BY vA.PatientCode

I need to know the columns DefaultProviderInitials and UDAS too. I think my results are accurate even though I'm allocating a MIN() and MAX() to them (it didn't seem to make a difference either way) although it is messy.

Answer 2

You've been given correct answers, but there is still one thing I'd like to point you to:

In your reply to my other answer you say: "... that I wanted to be able to SELECT extra columns from the vwTreatmentPlans table". But there can be many records for one patientcode in that table. If your criteria (accepted = 1 and current = 1) guarantees that you only get one record per patientcode, then no problem. If, however, you can still get more than one record, you would have to decide for aggregated data from that table (e.g. a minimum value or a sum). This can get complicated with many records per patientcode from both tables. This query for example would give you a wrong sum (because the values would get multiplied with the number of matching vA records):

SELECT
  vT.PatientCode, 
  MAX(vA.appointmentDateTimevalue) as max_date,
  SUM(vT.amount) as total_amount
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;

In such case you would want to join pre-aggregated data rather then the original records:

SELECT
  vT.PatientCode, 
  vA.max_date,
  vT.total_amount
FROM 
(
  SELECT PatientCode, SUM(amount) as total_amount
  FROM vwTreatmentPlans
  WHERE Current = 1
    AND Accepted = 1
  GROUP BY PatientCode
) vT
INNER JOIN 
(
  SELECT PatientCode, MAX(appointmentDateTimevalue) as max_date
  FROM vwAppointmentDetails
  GROUP BY PatientCode
) vA ON vT.PatientCode = vA.PatientCode
ORDER BY vt.PatientCode;

Answer 3

by grouping on both you are killing the max

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

Answer 4

All the data you show (PatientCode and appointmentDateTimevalue) is available in table vwAppointmentDetails. So select from that table.

The criteria which records to select are in table vwTreatmentPlans, so have it in the where clause.

select patientcode, max(appointmentdatetimevalue)
from vwappointmentdetails
where patientcode in
(
  select patientcode
  from vwtreatmentplans
  where patientcode in (123, ...)
  and current = 1
  and accepted = 1
)
group by patientcode;

No need to join here. This makes the query both very readable and maintainable. Disappointingly simple? :-)

Answer 5

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