previous-week-attendance.sql

/*
'A weekly summative report of attendance and lates for the school.'

    CASE ABSENT_STATUS
      WHEN 0 THEN 'Not absent/Unexplained'
      WHEN 1 THEN 'Explained - Unverified'
      WHEN 2 THEN 'Explained - Verified'
      WHEN 3 THEN 'Explained - Verified (Sick)'
      WHEN 4 THEN 'Explained - Verified (Suspended)'
      WHEN 5 THEN 'Explained - Verified (Approved By Principal)'
      WHEN 6 THEN 'Explained - Verified (School Business)'
      WHEN 7 THEN 'Explained - Verified (Flexible Timetable)'
      WHEN 8 THEN 'Explained - Verified (External Education)'
      WHEN 9 THEN 'Explained - Verified (Exempt)'
    END AS "ABSENT_STATUS_HR1",

To do: Split to seperate columns for SXW referencing
*/

SELECT

  FORM_RUN.FORM_RUN || ' (' || GENDER.GENDER || 's)' AS "FORM_GENDER",
  COUNT(ATTENDANCE_STATUS_DAILY.DAILY_ATTENDANCE_STATUS) AS DAILY_ABSENCES_COUNT,
    COUNT(ATTENDANCE_STATUS_AM.DAILY_ATTENDANCE_STATUS) AS LATE_COUNT

FROM 
    TABLE(EDUMATE.GET_ENROLED_STUDENTS_FORM_RUN(CURRENT_DATE)) A

  INNER JOIN FORM_RUN ON A.FORM_RUN_ID = FORM_RUN.FORM_RUN_ID
    INNER JOIN STUDENT ON A.STUDENT_ID = STUDENT.STUDENT_ID
    INNER JOIN CONTACT ON STUDENT.CONTACT_ID = CONTACT.CONTACT_ID
    INNER JOIN GENDER ON CONTACT.GENDER_ID = GENDER.GENDER_ID

    INNER JOIN DAILY_ATTENDANCE ON A.STUDENT_ID = DAILY_ATTENDANCE.STUDENT_ID

    LEFT JOIN DAILY_ATTENDANCE_STATUS ATTENDANCE_STATUS_DAILY ON ATTENDANCE_STATUS_DAILY.DAILY_ATTENDANCE_STATUS_ID = DAILY_ATTENDANCE.DAILY_ATTENDANCE_STATUS_ID
            AND ATTENDANCE_STATUS_DAILY.DAILY_ATTENDANCE_STATUS LIKE '%Absence%' AND ATTENDANCE_STATUS_DAILY.DAILY_ATTENDANCE_STATUS NOT LIKE '%Partial Absence%'
            AND ATTENDANCE_STATUS_DAILY.DAILY_ATTENDANCE_STATUS_ID NOT IN (20,21,22,23,24,25,26,27)
      
    LEFT JOIN DAILY_ATTENDANCE_STATUS ATTENDANCE_STATUS_AM ON ATTENDANCE_STATUS_AM.DAILY_ATTENDANCE_STATUS_ID = DAILY_ATTENDANCE.AM_ATTENDANCE_STATUS_ID
            AND ATTENDANCE_STATUS_AM.DAILY_ATTENDANCE_STATUS LIKE '%Late%'
        AND ATTENDANCE_STATUS_AM.DAILY_ATTENDANCE_STATUS_ID NOT IN (28,29,30,31)

WHERE
    DAILY_ATTENDANCE.DATE_ON >= (CURRENT_DATE - 7 DAYS)
  AND (ATTENDANCE_STATUS_DAILY.DAILY_ATTENDANCE_STATUS IS NOT NULL OR ATTENDANCE_STATUS_AM.DAILY_ATTENDANCE_STATUS IS NOT NULL)

GROUP BY FORM_RUN, GENDER

ORDER BY FORM_RUN, GENDER DESC