Attendance query

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

Comments

  • 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:
    1. Date
    2. Total student count for all enroled students
    3. 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
  • edited December 2017
    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
    
    admin
  • 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
    sandra
  • Roman,

    That is wonderful to hear...thank you so much for sharing this story : ) We are proud to be part of this along side you, and will keep working to minimise frustration and maximise joy. I am sure @sandra and @andystat will be proud too!

    Ross
    sandra
Sign In or Register to comment.