title: SQL query to identify students who are absent 5 consecutive school days in a row
body: <p>Using MySQL 2012, I need to identify students who are absent from school 5 days or more in a row, using the student information system where attendance is recorded.</p>
  
  <p>I have the ‘Day’ table that holds all the dates that are school days for the year (excludes weekends, holidays, etc.), and I build a temp table to hold the values I want.</p>
  
  <p>I am not getting how to format all this and I hope it's readable!</p>
  
  <pre><code>SELECT *
  INTO #GetAttDates
  FROM
  (
  SELECT convert(date,Date)AS AttendanceDate 
  FROM Day
  WHERE calendarID = 2062
  AND attendance = 1
  )x
  </code></pre>
  
  <p>Output snippet:</p>
  
  <pre><code>AttendanceDate
  8/30/2017
  8/31/2017
  9/1/2017
  9/5/2017
  9/6/2017
  9/7/2017
  9/8/2017
  9/11/2017
  9/12/2017
  9/13/2017
  9/14/2017
  9/15/2017
  </code></pre>
  
  <p>I have a view that I can pull the absences for each student from. I build a couple of temp tables from the view – ending with this temp table that gets me to the dates to compare against the school ‘Day’ table.</p>
  
  <pre><code>SELECT *
  INTO #TempAttendanceThree
  FROM
  (
  select personID
  , wholeDayAbsence
  , halfDayAbsence
  , currDate
  , status
  , excuse
  , description
  , case when netMinutes &lt; halfDayAbsence  Then 0
      when (netMinutes &gt;= halfDayAbsence) and (netMinutes &lt; wholeDayAbsence) then .5
    else 1
    end as AbDays
  from #TempAttendanceTwo 
  where case when netMinutes &lt; halfDayAbsence  Then 0
      when (netMinutes &gt;= halfDayAbsence) and (netMinutes &lt;     wholeDayAbsence) then .5
    else 1
  end = 1
  group by personID
  , wholeDayAbsence
  , halfDayAbsence
  , currDate
  , status
  , excuse
  , description
  , netMinutes
  )x
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>personID    wholeDayAbsence halfDayAbsence  currDate    status  excuse  description AbDays
  89  265 156 9/18/2017   A   E   Absent Excused  1
  89  265 156 10/5/2017   A   E   Absent Excused  1
  89  265 156 10/16/2017  A   X   Field Trip  1
  537 265 156 9/6/2017    A   E   Dismissed   1
  18889   265 156 9/12/2017   A   U   Absent Unexcused    1
  18889   265 156 9/13/2017   A   U   Absent Unexcused    1
  18889   265 156 9/14/2017   A   U   Absent Unexcused    1
  18889   265 156 9/19/2017   A   U   Absent Unexcused    1
  18889   265 156 9/20/2017   A   U   Absent Unexcused    1
  18889   265 156 9/22/2017   A   U   Absent Unexcused    1
  18889   265 156 9/26/2017   A   U   Absent Unexcused    1
  18889   265 156 9/27/2017   A   U   Absent Unexcused    1
  18889   265 156 9/28/2017   A   U   Absent Unexcused    1
  18889   265 156 9/29/2017   A   U   Absent Unexcused    1
  18889   265 156 10/2/2017   A   U   Absent Unexcused    1
  18889   265 156 10/3/2017   A   U   Absent Unexcused    1
  18889   265 156 10/4/2017   A   U   Absent Unexcused    1
  18889   265 156 10/5/2017   A   U   Absent Unexcused    1
  18889   265 156 10/10/2017  A   U   Absent Unexcused    1
  18889   265 156 10/11/2017  A   U   Absent Unexcused    1
  18889   265 156 10/12/2017  A   U   Absent Unexcused    1
  18889   265 156 10/13/2017  A   U   Absent Unexcused    1
  18889   265 156 10/16/2017  A   U   Absent Unexcused    1
  18889   265 156 10/17/2017  A   U   Absent Unexcused    1
  18889   265 156 10/18/2017  A   U   Absent Unexcused    1
  18889   265 156 10/19/2017  A   U   Absent Unexcused    1
  18889   265 156 10/20/2017  A   U   Absent Unexcused    1
  18889   265 156 10/23/2017  A   U   Absent Unexcused    1
  18889   265 156 10/24/2017  A   U   Absent Unexcused    1
  </code></pre>
  
  <p>This brings me to where I’m stuck. </p>
  
  <p>How to read the records for one personID and compare the dates against the ‘Day’ table dates to see if there are 5 or more absences in a row. </p>
  
  <p>Clearly ID #18889 should be identified as meeting that criteria.</p>
  
  <p>I envision the comparisons to work something like this behind the scenes when comparing the absent dates against the ‘Day’ attendance dates:</p>
  
  <pre><code>AttendanceDate  89  537 18889
  8/30/2017            
  8/31/2017            
  9/1/2017             
  9/5/2017             
  9/6/2017        9/6/2017     
  9/7/2017             
  9/8/2017             
  9/11/2017            
  9/12/2017           9/12/2017
  9/13/2017           9/13/2017
  9/14/2017           9/14/2017
  9/15/2017            
  9/18/2017   9/18/2017        
  9/19/2017           9/19/2017
  9/20/2017           9/20/2017
  9/21/2017           9/22/2017
  9/22/2017            
  9/25/2017            
  9/26/2017           9/26/2017
  9/27/2017           9/27/2017
  9/28/2017           9/28/2017
  9/29/2017           9/29/2017
  10/2/2017           10/2/2017
  10/3/2017           10/3/2017
  10/4/2017           10/4/2017
  10/5/2017   10/5/2017       10/5/2017
  10/10/2017          10/10/2017
  10/11/2017          10/11/2017
  10/12/2017          10/12/2017
  10/13/2017          10/13/2017
  10/16/2017  10/16/2017      10/16/2017
  10/17/2017          10/17/2017
  10/18/2017          10/18/2017
  10/19/2017          10/19/2017
  10/20/2017          10/20/2017
  10/23/2017          10/23/2017
  10/24/2017          10/24/2017
  </code></pre>
  
  <p>I need a query that will count the dates (by personID) and output the results, which would look like this:</p>
  
  <pre><code>personID    status  Consecutive Days
  89  A   1
  537 A   1
  18889   A   19
  </code></pre>