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'
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…