下面是一个你可以尝试的例子。。。

IF OBJECT_ID('tempdb..#Appointments') IS NOT NULL DROP TABLE #Appointments
IF OBJECT_ID('tempdb..#Visits') IS NOT NULL DROP TABLE #Visits

SELECT * INTO #Appointments FROM (VALUES
    (6, 'Fred', CAST('2020-07-22' as datetime)),
    (6, 'Fred', '2020-09-22'),
    (6, 'Fred', '2015-05-14'),
    (7, 'Barney', '2020-07-25'),
    (8, 'Wilma', '2020-08-15'),
    (9, 'Betty', '2020-09-10')
) S(patient_id, patient_name, appt_date)

SELECT * INTO #Visits FROM (VALUES
    (6, CAST('2020-07-23' as datetime), 'BP: 51, HR: 56', 1),
    (6, '2020-09-25', 'BP: 52, HR: 56', 2),
    (6, '2015-05-22', 'BP: 53, HR: 56', 3),
    (7, '2020-07-27', 'BP: 54, HR: 56', 4),
    (7, '2020-08-22', 'BP: 55, HR: 56', 5),
    (8, '2020-08-22', 'BP: 56, HR: 56', 6),
    (8, '2020-09-02', 'BP: 57, HR: 56', 7),
    (8, '2020-10-31', 'BP: 58, HR: 56', 8),
    (9, '2020-09-11', 'BP: 59, HR: 56', 9),
    (9, '2020-10-21', 'BP: 60, HR: 56', 10)
) S(patient_id, vitals_date, vitals_info, visit_id)

SELECT * FROM #Appointments
SELECT * FROM #Visits

SELECT
    A.*,
    V.*
FROM
    #Appointments A
    LEFT JOIN #Visits V ON
        V.visit_id = (
            SELECT TOP 1 V2.visit_id FROM #Visits V2 WHERE V2.patient_id = A.patient_id AND V2.vitals_date >= A.appt_date ORDER BY V2.vitals_date
        )

--更新以更改日期格式

您可以从vitaldate>=apptdate的就诊表中获取顶部（1）进行选择。即：

select a.PatientId, a.ApptDate, 
    v.VitalDate, v.vitalInfo, v.VitalId
from appointments a
   outer apply (select top(1) VitalDate, vitalInfo, VitalId 
                from Visits v 
                where a.PatientId = v.PatientId 
                  and v.VitalDate >= a.apptDate
                order by VitalDate) v(VitalDate, vitalInfo, VitalId);

编辑：后来我看到你说你可以复制。在现实中，这种情况并不是发生在“指定”患者就诊时。不管怎样，为了覆盖这些副本，我添加了另一个副本：

select a.PatientId, a.ApptDate, 
    v.VitalDate, v.vitalInfo, v.VitalId
from appointments a
   outer apply (select VitalDate, vitalInfo, VitalId 
                from Visits v1 
                where a.PatientId = v1.PatientId 
                  and v1.VitalDate = (select min(Vitaldate) from 
                    Visits v2 
                    where v1.patientId = v2.patientId
                         and v2.VitalDate >= a.apptDate)) v(VitalDate, vitalInfo, VitalId);

附言：这是dbfiddle演示