Hi folks,
I tried to come up with a query that gives me a daily student count based on attendance. After several hours of trying to make this work, I’m finally giving up.
Has anybody done something like this before?
Kind regards,
Roman
Hi Roman,
Attendance is a tricky one, the logs generally get looped over in PHP to find the end-of-day status for each student before attendance totals can be counted. It may still be possible as a query though with some creative table joins, what kind of query results are you hoping to see: totals for one day at a time, or a whole date range in one query?
Hi Sandra,
The purpose of this query is statistics. We do have issues with student attendance and we want to find ways to improve this. Such a query would help us to understand things better.
The date range can be alltime.
I need a list made up of three columns:
- Date
- Total student count for all enroled students
- Total student count for all enroled students having end-of-day status “Present”, “Present - Late” or “Present - Offsite”
Sounds really simple but it’s not!
Kind regards,
Roman
Hi Roman,
Here’s a query that should do what you’re looking for, modified from the one on the Attendance Trends page (be sure to check that out if its handy to see a visual graph of attendance over time).
It may take a couple seconds to run because there’s lots of attendance logs and a couple interesting joins in the query. The query is currently set to grab the total student enrolment + attendance present counts for the previous 30 days.
`SELECT attendanceLog.date, COUNT(DISTINCT gibbonPerson.gibbonPersonID) as `Total Student Enrolment`, attendanceLog.totalPresent as `Total Students Present` FROM gibbonStudentEnrolment
JOIN gibbonPerson ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID)
JOIN gibbonSchoolYear ON (gibbonStudentEnrolment.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID)
LEFT JOIN (
SELECT l.date, count(DISTINCT CASE WHEN (l.type='Present' OR l.type='Present - Late' OR l.type='Present - Offsite') THEN l.gibbonPersonID END) as totalPresent
FROM gibbonAttendanceLogPerson l
INNER JOIN (SELECT gibbonPersonID, date, MAX(timestampTaken) as maxTimestamp FROM gibbonAttendanceLogPerson GROUP BY gibbonPersonID, date) AS log ON (l.gibbonPersonID=log.gibbonPersonID AND l.date=log.date)
WHERE l.timestampTaken=log.maxTimestamp
GROUP BY l.date ORDER BY l.date
) AS attendanceLog ON (attendanceLog.date>=DATE_SUB(NOW(), INTERVAL 30 DAY) AND attendanceLog.date<=NOW())
WHERE gibbonSchoolYear.status='Current' AND gibbonPerson.status='Full'
AND (gibbonPerson.dateStart IS NULL OR gibbonPerson.dateStart<=attendanceLog.date) AND (gibbonPerson.dateEnd IS NULL OR gibbonPerson.dateEnd>=attendanceLog.date)
GROUP BY attendanceLog.date
`
```
Hi Sandra,
That’s quite a mouthful and clearly demonstrates your advanced sql programming skills. I can’t say that I fully understand the query yet. I’m still playing in a lower league.
What jumps immediately into my eye is that you are restricting results to gibbonPerson.status=‘Full’. This works perfectly if you are interested in current data but not when you want to get historic data. I know, this is common practice in Gibbon, however, a somehow bad one that I personally have never fully understood also causing Gibbon not being able to display historic data correctly. Have you come across this experience as well, e.g. after the rollover to the next year?
Instead of gibbonPerson.status=‘Full’ I have started to use e.g. (dateStart IS NULL OR dateStart<=‘2017-11-30’) AND (dateEnd IS NULL OR dateEnd>=‘2017-11-30’). This works well for me keeping historic data intact. However, it also requires that anytime a student is set to „Left” his dateEnd field is set as well. I ensure this with a little hacking in javascript.
Kind regards,
Roman
Hi Roman,
Yes, Sandra is an SQL master!
This question of which students to show where and when has no correct answer, and the database and interface design in Gibbon are based on experiences at my own school, and our assessment of what information to see when.
We use the application form and the User Management CLI to make sure that start and end dates are always set, and aligned to the status.
I do understand your frustrations, but this one is a double-edged sword: if you ignore the status, you start seeing data that you might not want to see in a given context.
Thanks for sharing your thoughts.
Ross
Hi Ross,
I’m not that frustrated. Software is never perfect, but Gibbon is likely the best out there in regard to clean coding and hackability. I have never regretted the decision to go for Gibbon.
What we were able to achieve in our school using Gibbon is rather amazing and it makes me proud. All this wouldn’t have been possible without you not forgetting the help of Sandra and Andy of course.
Currently up to 20 teachers are working in our DebianEdu powered computer lab, taking missing attendances and editing terminal reports. At the same time the finance office is receiving the final installments of student bills and is issuing receipts. Administration is doing final changes to student demographics, which will appear on the reports and is supervising progress of the work in real time to ensure the dead lines can be met.
These are exciting times!
Kind regards,
Roman