SELECT Person, Absent, Attended, Absent/(Absent + Attended) As PercentageAbsent
FROM
(
SELECT Person, SUM(CASE WHEN Attendance IN 'ABSENT'
                            THEN 1
                            ELSE 0
                        END) As Absent,
                    SUM(CASE WHEN Attendance not in 'ABSENT'
                            THEN 1
                            ELSE 0
                        END) As Attended
FROM db.attendance
WHERE day_id BETWEEN 3486 AND 3669
GROUP BY Person
)
WHERE Absent >= 5
GROUP BY Person, Absent, Attended
ORDER BY PercentageAbsent DESC;

---------------------------------------------------------------------------
CREATE TABLE #tmp(dayid INT, person VARCHAR(32), attendance VARCHAR(32));

INSERT INTO #tmp VALUES
(1,'NOZ2k','Absent'),
(2,'NOZ2k','Absent'),
(5,'NOZ2k','Attended'),
(6,'NOZ2k','Absent'),
(10,'NOZ2k','Attended'),
(11,'NOZ2k','Absent'),
(12,'NOZ2k','Absent'),
(18,'NOZ2k','Absent');


---------------------------------------------------------------------------
;WITH cte1 AS
(
   SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY person ORDER BY dayId) AS RN
   FROM
      #tmp
),
cte2 AS
(
   SELECT
      *,
      RN - ROW_NUMBER() OVER(PARTITION BY person,Attendance ORDER BY dayId) AS grp
   FROM
      cte1
)
SELECT
   person,
   COUNT(*) AS Consecutive_Absence
FROM
   cte2
WHERE
   Attendance = 'Absent'
GROUP BY
   person,
   grp;
 
---------------------------------------------------------------------------
DROP TABLE #tmp;
---------------------------------------------------------------------------