SQL Server : finding consecutive absence counts for students over custom dates

Question

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 .

The data is like

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

In the above case the result should be

StudentId        AbsenceStartDate     AbsenceEndDate     ConsecutiveAbsences
----------------------------------------------------------------------------
178234           5/1/2017             11/1/2017           3

I have tried to implement this solution Calculating Consecutive Absences in SQL However that only worked for dates in order only. Any suggestions will be great, thanks

Answer 1

Oh, you have both absences and presents in the table. You can use the difference of row_numbers() approach:

select studentid, min(date), max(date)
from (select a.*,
             row_number() over (partition by studentid order by date) as seqnum,
             row_number() over (partition by studentid, attendance order by date) as seqnum_a
      from attendance a
     ) a
where attendance = 'A'
group by studentid, (seqnum - seqnum_a)
having count(*) >= 3;

The difference of row numbers gets consecutive values that are the same. This is a little tricky to understand, but if you run the subquery, you should see how the difference is constant for consecutive absences or presents. You only care about absences, hence the where in the outer query.