title: Get latest date before date value in row
body: <p>I am using MSSQL 2008 R2. I am attempting to get the latest data date up to a date stored in another column.  I am able to pull the latest data date overall using max(ProcedureDate) in a subquery; however, i need the latest date before the date that's stored in the column.</p>
  
  <p>Here's an example:</p>
  
  <pre><code>Current Table: Procedures       
  
  ID Patient  ProcedureType   ProcedureDate
  1  George   ExamA           1/1/2013
  2  George   TreatmentA      1/3/2013
  2  George   TreatmentB      1/5/2003
  4  George   ExamB           2/1/2013
  5  George   TreatmentA      2/5/2013
  
  Desired Table:  ProceduresWithLastExam
  
  ID Patient  ProcedureType   ProcedureDate   LastExamDate    DaysSinceLastExam    LastExamType
  1  George   ExamA           1/1/2013        1/1/2013        0                    ExamA
  2  George   TreatmentA      1/3/2013        1/1/2013        2                    ExamA
  3  George   TreatmentB      1/5/2013        1/1/2013        4                    ExamA
  4  George   ExamB           2/1/2013        2/1/2013        0                    ExamB
  5  George   TreatmentA      2/5/2013        2/1/2013        4                    ExamB
  </code></pre>
  
  <p>I have attempted using the following, but it only pulls back the latest data date for that Patient.</p>
  
  <pre><code>select p.*, a.LastExamDate, a.ProcedureType as LastExamType from Procedures p
  left join (
     select exams.Patient, exams.ProcedureType, MAX(exams.ProcedureDate) as LastExamDate from Procedures exams
     where ProcedureType like 'Exam%'
     group by exams.Patient, exams.ProcedureType
  )a
  on p.Patient = a.Patient
  </code></pre>
  
  <p>The results for all rows is 2/1/13 as LastExamDate and ExamB as LastExamType.</p>
  
  <p>I have attempted including some additional date parameters in my left join, in my where clause, and in my subquery to no success.</p>
  
  <p>Please note I have omitted datediff logic until I can get the correct dates to return.</p>
  
  <p>Thanks in advance for the help.</p>