title: Using a Correlated Subquery within a Left Join
body: <p>I need a fresh set of eyes on this query. Without getting mega in depth in this code my problem is I'm doing a left join to pull from the TXP_Digital_Signatures (tds) table which stores signatures to the most current version of Treatment Plans (txp_master txp). What this code is doing is bringing back results where tds.signed is null (no signature) or marked N (No). This works, but what this report has done is show people what No's need to become yes, but that is leaving the No left behind, so if there is a more recent Yes then the No in that version of the tds.plan_id it is still pulling that plan_id where I no longer want it where the most recent signature status is a Y (yes), etc. The code snippet below added to the where statement works, but it hides all No's even if there is not a newer Y (yes).</p>
  
  <pre><code>tds.date = (select Max(date) from TXP_Digital_Signatures where tds.plan_id = txp.plan_id)
  </code></pre>
  
  <p>Can anyone think of a way to either add a correlated subquery to the left join, so it only pulls the max(tds.date) for each tds.plan_id or how to rework my where statements so the no's without a newer yes and the null's still show up. I really don't want to redo the entire report as a grouped report if I can help it where I feel it'll break a ton of stuff on me and basically have me redoing this report from scratch. SQL 2008 R2</p>
  
  <pre><code>SELECT  case_status,
      CONVERT(CHAR(10), episode_open_date, 101)AS 'Enrolled' ,
      txp.patient_id, 
      p.lname+', ' + p.fname AS 'Client',
      CONVERT(CHAR(10), txp.effective_date, 101)AS 'Effective', 
      CONVERT(CHAR(10), next_review_date, 101)AS 'Review',
      txp.signed,
      (SELECT location_code FROM staff s WHERE s.staff_id = txp_coordinator_id) AS 'Clinic',
      (SELECT s.lname+', ' +s.fname FROM staff s WHERE s.staff_id = txp_coordinator_id) AS 'Coordinator',
      (SELECT s.lname+', ' +s.fname FROM staff s WHERE s.staff_id = ts.team_member_id ) AS 'Team', 
      ts.signed,
      tds.signed as 'Patient Sig'
  FROM txp_master txp join patient p ON p.patient_id = txp.patient_id and p.episode_id = txp.episode_id
      join txp_signature ts on ts.plan_id = txp.plan_id and ts.version_no = txp.version_no and ts.team_member_id &lt;&gt; txp.txp_coordinator_id
      left join TXP_Digital_Signatures tds on tds.plan_id = txp.plan_id
  
  where p.case_status = 'A' and 
  txp.status &lt;&gt; 'er' and patient_signed_date is null 
  and tds.signed is null or tds.signed = 'N'
  and txp.effective_date &gt; '2016-12-31 00:00:00.000'
  and tds.date = (select Max(date) from TXP_Digital_Signatures where tds.plan_id = txp.plan_id)
  
  order by patient_id
  </code></pre>