Today at work I faced a dilemma. I was tasked with developing a function in which a parent could be notified when their child has an absence or a tardy in one of their classes (I work for a school district). It involved touching 3 different tables including one twice. The three types of tables are as follows, an attendance details table which we'll call AttendanceDetail, a parent/student table which ties parents with students which we'll call ParentStudent, and finally a notify parent when an absence occurs table which we'll call AttendanceNotification.
So here is the short of how the process works. Daily a routine will run checking to see if a student whose parent requested a notification upon an attendance event had missed a class and/or was tardy to class. Sounds simple enough huh? Well it wasn't as easy as first thought.
My first query was as follows...
SELECT an.studentID, ps.studentFirstName, ps.studentLastName, ad.absence_date
FROM AttendanceNotification an
INNER JOIN ParentStudent ps ON ps.studentID = an.studentID
INNER JOIN AttendanceDetail ad ON ad.studentID = an.studentID
WHERE an.notify = 1 AND ps.status = 'A'
Basically this query grabs all prior absence events for a student where they are active and the parent needs to be notified of the last absence event. So we have a bunch of unwanted events here, we just need the most recent event.
Simple enough we'll just add a couple things to our query and we'll be good to go right? Not so fast. So I added a MAX() function to the absence date which would give me the most recent absence and I grouped by the studentID which would only give me one record back per student and I ended up with the following.
SELECT an.studentID, ps.studentFirstName, ps.studentLastName, MAX(ad.absence_date) AS absenceDate
FROM AttendanceNotification an
INNER JOIN ParentStudent ps ON ps.studentID = an.studentID
INNER JOIN AttendanceDetail ad ON ad.studentID = an.studentID
WHERE an.notify = 1 AND ps.status = 'A'
GROUP BY an.studentID, ps.studentFirstname, ps.studentLastname
Well, the problems began to start, I needed to be able to show the parent what the absence event detail was. Did their student have a tardy? An unexcused absence? A doctors appointment? You get the point. My first thought was to just add it into the SELECT statement for the above query, but then I had to include them in my GROUP BY clause which caused more than one record per student to be returned. I then thought about trying to wrap them in a MAX function, that didn't work, they needed an aggregate function to be included in my SELECT criteria and not in my GROUP BY, I was at a loss.
Then I got an idea, I wondered if I could JOIN on a sub query. I had never tried it before, but it worked perfectly! I thought I'd share it with you just in case you ever ran across a similar problem.
The final query does an INNER JOIN from the AttemdanceDetail table to the previous result set or sub query. Now we're able to get detailed absence info yet only the most recent record. Here is the final query.
SELECT
a.studentID,
a.studentFirstName,
a.studentLastName,
a.absenceDate,
CASE WHEN ltrim(rtrim(adII.am_office_abs)) IS NULL THEN ltrim(rtrim(adII.am_teacher_abs)) ELSE ltrim(rtrim(adII.am_office_abs)) END AS am,
CASE WHEN ltrim(rtrim(adII.pm_office_abs)) IS NULL THEN ltrim(rtrim(adII.pm_teacher_abs)) ELSE ltrim(rtrim(adII.pm_office_abs)) END AS pm
FROM AttendanceDetail adII
INNER JOIN
(SELECT an.studentID, ps.studentFirstName, ps.studentLastName, MAX(ad.absence_date) AS absenceDate
FROM AttendanceNotification an
INNER JOIN ParentStudent ps ON ps.studentID = an.studentID
INNER JOIN AttendanceDetail ad ON ad.studentID = an.studentID
WHERE an.notify = 1 AND ps.status = 'A'
GROUP BY an.studentID, ps.studentFirstname, ps.studentLastname) a
ON a.studentID = adII.studentID AND a.absenceDate = adII.absence_date
Perhaps there is a better way to do this, I just don't know how, but this worked so I ran with it.
Comments (2)
