Attendance Reports

Administration
1

For inspection purposes, we need to supply attendance data for students. One example of this is an attendance summary for current and previous school years. In the UK, we need to register students twice a day so I have created two classes “AM registration” and “PM registration” so that we can take attendance twice each day. What would be the best way to quickly gather attendance summary data for these two classes both for current and previous school years.

2

Good question, and not one we’ve encountered before. Are you looking for a complete listing of students and their daily attendance, or some kind of summary data? Thanks!

3

We need a listing of each student with both their attendances on every date! Luckily we only have 36 students but even so: The column headings would be the student name then each date with AM and PM within the range requested. For each AM and PM we would need the attendance code. They could ask about this academic year but also previous years!

4

Hi sdbaldwin,

This may be tricky to achieve with a query alone, as MySQL doesn’t handle dynamic columns very easily, the solution would likely need to be PHP based. Do you have capacity on your end to create this? There are some reports in the Attendance module that could be used as a starting point, but it would certainly require some PHP and database knowledge. Another option, if you have someone who is skilled in spreadsheet formulas, is to export the data through a query and use spreadsheet formulas to count and tally up the results.

5

Hi Sandra,
I am not a whizz at PHP, MySQL or databases generally unfortunately. A spreadsheet sounds a good idea though. As long as I understand what data I am getting, I should be able to process it into a usable form. Is it quite easy to get it out for previous years as well as current ones. All I really need is student name and their attendance data for both AM and PM registration on each day between certain dates. If a query could be written for this and a report loaded into a spreadsheet it should be easy enough to process the data and get the required information.

6

Hi sdbaldwin,

The following query may help get you started. It grabs the logs for a range of dates, groups them by student, and looks for an AM and PM attendance by checking the time range of attendance taken. I don’t have a ton of data to test this with, but I think it’s close to what you need. Hope it helps!

 

`SELECT 
    gibbonPerson.surname, 
    gibbonPerson.preferredName, 
    gibbonPerson.username, 
    IFNULL(attendanceAM.date, attendancePM.date) as `Date`, 
    attendanceAM.type AS `AM Attendance`, 
    attendancePM.type AS `PM Attendance`
FROM gibbonPerson
LEFT JOIN gibbonAttendanceLogPerson AS attendanceAM ON (gibbonPerson.gibbonPersonID=attendanceAM.gibbonPersonID AND TIME(attendanceAM.timestampTaken) BETWEEN '00:00:00' AND '12:00:00')
LEFT JOIN gibbonAttendanceLogPerson AS attendancePM ON (gibbonPerson.gibbonPersonID=attendancePM.gibbonPersonID AND TIME(attendancePM.timestampTaken) BETWEEN '12:00:01' AND '23:59:59')
WHERE (attendanceAM.date IS NULL OR attendanceAM.date BETWEEN '2019-09-01' AND '2019-12-01') AND 
(attendancePM.date IS NULL OR attendancePM.date BETWEEN '2019-09-01' AND '2019-12-01')
GROUP BY attendanceAM.gibbonPersonID, attendancePM.gibbonPersonID, attendanceAM.date
ORDER BY attendanceAM.date, gibbonPerson.surname, gibbonPerson.preferredName;`
```
7

Thank you so much, that is really useful. Is it easy to extend it to include students who have left so we have an attendance history?

8

I modified the query to look at the attendance data from last year but only got records from students who are still here. Is there a way to get the leaver’s data back?

9

@ross I forgot to say, you are awesome!