时间戳

首页 > 解决方案 > 查找同一人的最新约会日期

sql - 查找同一人的最新约会日期

问题描述

我想创建一个新列，显示同一 HC_Number.Example 的最新约会日期：HC_Number-2552 在 12 月 2 日、12 月 7 日和 12 月 25 日有 3 个约会。因此，显示最新约会的新列将显示 12 月 25 日，同时显示 12 月 2 日和 7 日的记录。

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'

标签： sql

解决方案

您可以使用OVER 子句 (Transact-SQL)获取每个 HC_NUMBER 的最新约会日期列MAX(a.APPOINTMENT_DATE) OVER(PARTITION BY a.HC_NUMBER) AS LATEST_APPOINTMENT_DATE。

尝试如下。

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'

或者，您可以将inner sql查询与GROUP BY HC_NUMBER&一起使用，MAX(APPOINTMENT_DATE)然后将其与您的表连接，如下所示。

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'

推荐阅读