Hopefully someone can help me here.
Effectively I am trying to count three different types of possible registration entry for students for a given day, and then perform maths using those counted values.
So far my SQL is as follows:
SELECT dbo.TblPupilManagementPupils.txtFullName, dbo.TblPupilManagementPupils.txtSchoolID FROM dbo.TblPupilManagementPupils where dbo.TblPupilManagementPupils.intSystemStatus = 1)
as PupDetails
LEFT JOIN (
SELECT COUNT(dbo.VwRegistrationSchoolRegistrationAttendance.txtRegistrationStatus) as presCount, dbo.VwRegistrationSchoolRegistrationAttendance.txtSchoolID
FROM dbo.VwRegistrationSchoolRegistrationAttendance
WHERE dbo.VwRegistrationSchoolRegistrationAttendance.txtRegistrationStatus = 'Present'
AND dtRegistrationDateTime > '03-12-2024 08:25:00' AND dtRegistrationDateTime < '03-12-2024 16:00:00'
GROUP BY dbo.VwRegistrationSchoolRegistrationAttendance.txtSchoolID
) as Present
ON PupDetails.txtSchoolID = Present.txtSchoolID
LEFT JOIN (
SELECT COUNT(dbo.VwRegistrationSchoolRegistrationAttendance.txtRegistrationStatus) as absentCount, dbo.VwRegistrationSchoolRegistrationAttendance.txtSchoolID
FROM dbo.VwRegistrationSchoolRegistrationAttendance
WHERE dbo.VwRegistrationSchoolRegistrationAttendance.txtRegistrationStatus = 'Absent'
AND dtRegistrationDateTime > '03-12-2024 08:25:00' AND dtRegistrationDateTime < '03-12-2024 16:00:00'
GROUP BY dbo.VwRegistrationSchoolRegistrationAttendance.txtSchoolID ) as Absent
ON PupDetails.txtSchoolID = Absent.txtSchoolID
LEFT JOIN (
SELECT COUNT(dbo.VwRegistrationSchoolRegistrationAttendance.txtRegistrationStatus) as notRegCount, dbo.VwRegistrationSchoolRegistrationAttendance.txtSchoolID
FROM dbo.VwRegistrationSchoolRegistrationAttendance
WHERE dbo.VwRegistrationSchoolRegistrationAttendance.txtRegistrationStatus = 'Not Registered'
AND dtRegistrationDateTime > '03-12-2024 08:25:00' AND dtRegistrationDateTime < '03-12-2024 16:00:00'
GROUP BY dbo.VwRegistrationSchoolRegistrationAttendance.txtSchoolID ) as NotReg
ON PupDetails.txtSchoolID = Absent.txtSchoolID
ORDER BY PupDetails.txtFullName
This gives me a list of all pupils, and how many absent, present, and not registered entries they have for the given day. So far so good.
But what I can’t figure out is how to then do maths using the count values. For example, how might I divide the number of present entries by the number of absent entries?