title: SQL Server : finding consecutive absence counts for students over custom dates
body: <p>I have a table which stores attendances of students for each day. I need to get students who are consecutively absent for 3 days. However, the dates when attendance is taken is not in a order, some days like nonattendance, holidays, weekends are excluded. The dates when students attended are the dates where records exist in that table .  </p>
  
  <p>The data is like </p>
  
  <pre><code>StudentId       Date           Attendance
  -----------------------------------------
  178234          1/1/2017          P
  178234          5/1/2107          A
  178234          6/1/2107          A
  178234          11/1/2107         A
  178432          1/1/2107          P
  178432          5/1/2107          A
  178432          6/1/2107          P
  178432          11/1/2107         A
  </code></pre>
  
  <p>In the above case the result should be</p>
  
  <pre><code>StudentId        AbsenceStartDate     AbsenceEndDate     ConsecutiveAbsences
  ----------------------------------------------------------------------------
  178234           5/1/2017             11/1/2017           3
  </code></pre>
  
  <p>I have tried to implement this solution <a href="https://stackoverflow.com/questions/1557067/calculating-consecutive-absences-in-sql">Calculating Consecutive Absences in SQL</a>  However that only worked for dates in order only. Any suggestions will be great, thanks</p>