获取行中日期值之前的最新日期

您可以使用OUTER APPLY。它就像一个相关的子查询，但是允许多个列：

SELECt  p.ID,         p.Patient,        p.ProcedureType,        p.ProcedureDate,        [LastExamDate] = exam.ProcedureDate,         [DaysSinceLastExam] = DATEDIFF(DAY, exam.ProcedureDate, p.ProcedureDate),        [LastExamType] = exam.ProcedureType FROM    Procedures p        OUTER APPLY        (   SELECt  TOP 1 exams.ProcedureType, exams.ProcedureDate FROM    Procedures exams WHERe   Exams.ProcedureType LIKE '%Exam%' AND     Exams.Patient = p.Patient AND     Exams.ProcedureDate <= p.ProcedureDate ORDER BY Exams.ProcedureDate DESC        ) exam;