title: Unable to get the required result using update statement
body: <p>I am unable to update the data using record by record below scenario. </p>
  
  <p>Required output: patient will able to Admit/Re-admit multiple times in hospital, if a patient readmitted multiple times in hospital after the first visit, first visit record will get Re-admission=0 and Index=1. This visit should cal Index_Admission of that patient. using this index_admission should calculate the 30-day readmission.</p>
  
  <p><strong>Current Output:</strong> 
  <img src="https://i.stack.imgur.com/ohTTz.jpg" alt="enter image description here"></p>
  
  <p>Calculation: 
  From index_admission discharge date to next admit_visit date, </p>
  
  <blockquote>
    <p>1) if the diff is having less than 30 days, readmission=1 and Index=0</p>
  
  <pre><code> else readmission=0 and Index=1 should be update.
  </code></pre>
  </blockquote>
  
  <p>For checking this every time should check using the latest index_admission discharge_date.</p>
  
  <p>To get this result i written below logic, but it's updating readmission=0 and Index=1 after 30-day post discharge of using first index admission.</p>
  
  <pre><code>UPDATE Readmission
  SET Index_AMI = (CASE
          WHEN DATEDIFF(DD, (SELECT
              Sub.Max_Index_Dis
          FROM (SELECT
              Patient_ID,
              MAX(Discharge_Date_Time) Max_Index_Dis
          FROM Readmission
          WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID
          GROUP BY Patient_ID) Sub), FPR.Admit_Date_Time) &lt;= 31 THEN 0 ELSE 1
      END),
      Is_AMI_Readmission = (CASE
          WHEN DATEDIFF(DD, (SELECT
              Sub.Max_Index_Dis
          FROM (SELECT
              Patient_ID,
              MAX(Discharge_Date_Time) Max_Index_Dis
          FROM Readmission
          WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID
          GROUP BY Patient_ID) Sub)
          , FPR.Admit_Date_Time) &lt;= 31 THEN 1 ELSE 0
      END)
  FROM Readmission FPR
  WHERE fpr.index_ami IS NULL
  </code></pre>
  
  <p><strong>Expected Result:</strong>
  <img src="https://i.stack.imgur.com/UApRL.jpg" alt="enter image description here"></p>
  
  <p>Please help me to resolve the issue.</p>
  
  <p>Best Regards,
  Nagendra</p>