title: Hospital readmission history in SQL Server
body: <p>I have a table which holds hospital admission date, discharge date and the diagnosis:</p>
  
  <p><a href="https://i.stack.imgur.com/LR2eo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LR2eo.png" alt="enter image description here"></a></p>
  
  <p>Now I am trying to get count of readmission within last one year and the history for each encounter (Discharge date + Diagnosis). The outcome should looks something like this:</p>
  
  <p><a href="https://i.stack.imgur.com/H75Zo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/H75Zo.png" alt="enter image description here"></a></p>
  
  <p>I am able to get the count but struggling with the history. This is my code:</p>
  
  <pre><code>SELECT
      A.Encounter, A.Patient, A.AdmissionDt, A.DischargeDt, 
      (SELECT COUNT(*) 
       FROM MyTable B 
       WHERE A.Patient = B.Patient 
         AND B.AdmissionDt &gt;= DATEADD(YY, -1, A.DischargeDt) 
         AND B.AdmissionDt &lt; A.DischargeDt) AS Cnt 
  FROM
      MyTable A 
  ORDER BY
      AdmissionDt DESC
  </code></pre>
  
  <p>Can I get some help please?</p>