Catch-all Attendance Query

Administration
1

Can I have a query added that shows:
School Days Present Between Two Dates by Student and the Percentage (Present)
With columns included for Present, Absent, Late, Left Early

I know this query will be similar to and would be combining (some aspects of) these existing queries:

Class Attendance as Percentage
Lateness Count in Last Week by Student
School Days Present Between Two Dates by Student

Thanks

2

Hi Tieku, unfortunately we’ve got our hands full at the moment, and so won’t be able to do this via the forums. If you want to email support@gibbonedu.com we can offer a quotation for this custom query, as well as an estimate of how long it might take to build. Cheers! Ross.

3

Hi Ross. I was able to tweak the original query to produce the result I was looking for. Thanks

4

Perfect, that is great to hear : )

5

@tiekubd would you mind sharing your tweak?

6

@ZACelliers

This is what I have:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT
gibbonPerson.gibbonPersonID AS ID,
CONCAT(gibbonPerson.firstName," ", gibbonPerson.surname) AS Name,
preferredName,
gibbonFormGroup.nameShort AS formGroup,
(SELECT COUNT(DISTINCT date) AS count FROM gibbonAttendanceLogPerson WHERE gibbonPersonID=ID AND direction=‘In’ AND (type=‘Present’ OR type=‘Present - Late’ OR type=‘Present - Online’) AND date>=:dateStart AND date<=:dateEnd GROUP BY gibbonPersonID ORDER BY gibbonPersonID) AS presentCount,
(SELECT COUNT(DISTINCT date) AS count FROM gibbonAttendanceLogPerson WHERE gibbonPersonID=ID AND direction=‘In’ AND (type=‘Present - Late’) AND date>=:dateStart AND date<=:dateEnd GROUP BY gibbonPersonID ORDER BY gibbonPersonID) AS LateCount
FROM gibbonPerson
JOIN gibbonStudentEnrolment ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID)
JOIN gibbonFormGroup ON (gibbonStudentEnrolment.gibbonFormGroupID=gibbonFormGroup.gibbonFormGroupID)
WHERE
status=‘Full’
AND gibbonStudentEnrolment.gibbonSchoolYearID=:schoolYear
ORDER BY
surname, preferredName ;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
You have to create 3 variables:
School Year
schoolYear

Start Date
dateStart

End Date
dateEnd

And please note I do not calculate the percentages in the query. I do that after the export in a spreadsheet.
I will see whether I can incorporate that as well.

Hope this helps

Tieku

7

Super, thanks Tieku. appreciate it.