sql - find the latest appointment date for the same person

Question

I want to create a new column, which shows the latest appointment date for the same HC_Number.Example: HC_Number- 2552 has 3 appointments in december- 2nd december, 7th december and 25th december. So the new column showing latest appointment will show 25th december for both the records on 2nd & 7th december.

Select 
a.HC_NUMBER
,a.APPOINTMENT_TYPE_FULL
,a.APPOINTMENT_DATE
,a.person_id
,a.APPT_BOOKED_BY

FROM [HIM_Clinical].[dbo].[APPOINTMENT_DATA] a (nolock) -----Main Data----

left join [ODS_CCL].[dbo].[ODS_CCL_ENCOUNTER] e (nolock) on a.ENCNTR_ID=e.ENCNTR_ID

WHERE a.APPOINTMENT_TYPE_FULL like '%Smart Checkup%'
AND a.Appointment_Status IN ('Checked Out','Checked In','No Show','Confirmed')
AND a.APPOINTMENT_DATE>='2020-12-01' AND a.APPOINTMENT_DATE<='2020-12-31'

question from:https://stackoverflow.com/questions/65914510/find-the-latest-appointment-date-for-the-same-person

Answer 1

You can use OVER Clause (Transact-SQL) to get latest appointment date column for each HC_NUMBER with MAX(a.APPOINTMENT_DATE) OVER(PARTITION BY a.HC_NUMBER) AS LATEST_APPOINTMENT_DATE.

Try like below.

Select a.HC_NUMBER
        ,a.APPOINTMENT_TYPE_FULL
        ,a.APPOINTMENT_DATE
        ,a.person_id
        ,a.APPT_BOOKED_BY
        ,MAX(a.APPOINTMENT_DATE) OVER(PARTITION BY a.HC_NUMBER) AS LATEST_APPOINTMENT_DATE

FROM [HIM_Clinical].[dbo].[APPOINTMENT_DATA] a (nolock) -----Main Data----

left join [ODS_CCL].[dbo].[ODS_CCL_ENCOUNTER] e (nolock) on a.ENCNTR_ID=e.ENCNTR_ID

WHERE a.APPOINTMENT_TYPE_FULL like '%Smart Checkup%'
    AND a.Appointment_Status IN ('Checked Out','Checked In','No Show','Confirmed')
    AND a.APPOINTMENT_DATE>='2020-12-01'
    AND a.APPOINTMENT_DATE<='2020-12-31'

---

Alternatively you can use inner sql query with GROUP BY HC_NUMBER & MAX(APPOINTMENT_DATE) then join it with your table as below.

Select a.HC_NUMBER
        ,a.APPOINTMENT_TYPE_FULL
        ,a.APPOINTMENT_DATE
        ,a.person_id
        ,a.APPT_BOOKED_BY
        ,latest.APPOINTMENT_DATE AS LATEST_APPOINTMENT_DATE

FROM [HIM_Clinical].[dbo].[APPOINTMENT_DATA] a (nolock) -----Main Data----

INNER JOIN (
    SELECT HC_NUMBER, MAX(APPOINTMENT_DATE) AS APPOINTMENT_DATE
    FROM [HIM_Clinical].[dbo].[APPOINTMENT_DATA] (nolock)
    GROUP BY HC_NUMBER
) latest ON latest.HC_NUMBER = a.HC_NUMBER

left join [ODS_CCL].[dbo].[ODS_CCL_ENCOUNTER] e (nolock) on a.ENCNTR_ID=e.ENCNTR_ID

WHERE a.APPOINTMENT_TYPE_FULL like '%Smart Checkup%'
    AND a.Appointment_Status IN ('Checked Out','Checked In','No Show','Confirmed')
    AND a.APPOINTMENT_DATE>='2020-12-01'
    AND a.APPOINTMENT_DATE<='2020-12-31'